Posted by deane.barker on 07/28/06 15:35

Thanks.

FYI -- I've learned in the meantime that MySQL has this functionality:

ORDER BY FIELD(my_field, 'value2','value1','value3')

Syntactic sugar, to be sure, but still handy.

Deane



Erland Sommarskog wrote:
> (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

• Next in forum: Re: Ordering results by order of the "IN' clause
• Prev in forum: xp_cmdshell/DTEXEC in SSIS
• Thread view: Re: Ordering results by order of the "IN' clause

[Reply to this message]