Posted by Erland Sommarskog on 07/28/06 07:32

(deane.barker@gmail.com) writes:
> Consider this SQL:
>
> SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1',
> 'value3')
>
> Simple enough, but is there anyway to specify that the result should be
> ordered exactly like the "IN" clause states? So when this recordset
> comes back, I want it like this:
>
> my_field
> ------------
> value2
> value1
> value3

No. The IN clause is just a syntactic shortcut for a bunch of OR operators.
You will need to add explicit ordering, for instance:

ORDER BY CASE my_field WHEN 'value2' THEN 1
WHEN 'value1' THEN 2
WHEN 'value3' THEN 3
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

[Back to original message]