Posted by Erland Sommarskog on 11/05/06 15:17

Yobbo (info@SpamMeNot.co.uk) writes:
> My query is as follows:
>
> SELECT STRINGTEXT, TOKENID
> FROM WEBSTRINGS
> WHERE TOKENID IN (6,20,234,19,32,4,800,177)
>
> All I want is my resultset to come back in the order that I have defined
> in the IN clause, but unfortunately SQL is trying to be too helpful and
> sorts the numbers in the IN clause so that the resultset comes back with
> a TOKENID order of 4,6,19,20,32,177,234,800.
>
> I don't want this bloody order I want 6,20,234,19,32,4,800,177!!
>
> Sorry for my rant, but its got my hot under the collar.

Actually, SQL Server does not sort at all. It just retrieves the rows
in the order which happens to be most efficient.

As an alterantive to Dan's suggestion, this may or may not fit better
to your actual problem:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)
ORDER BY CASE TOKENID
WHEN 6 THEN 1
WHEN 20 THEN 2
WHEN 234 THEN 3
-- etc
END



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Databases in SQL Server
• Prev in forum: Re: Getting back set order from the IN param
• Thread view: Re: Getting back set order from the IN param

[Reply to this message]