Ordering results by order of the "IN' clause

[deane.barker@gmail.com]

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

Possible?

Deane

[Erland Sommarskog]

Re: Ordering results by order of the "IN' clause

(deane.barker@g mail.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@sommarsk og.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

[deane.barker@gmail.com]

Re: Ordering results by order of the "IN' clause

Thanks.

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

ORDER BY FIELD(my_field, 'value2','value 1','value3')

Syntactic sugar, to be sure, but still handy.

Deane



Erland Sommarskog wrote:

(deane.barker@g mail.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@sommarsk og.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/prodinf...ons/books.mspx

[Alexander Kuznetsov]

Re: Ordering results by order of the "IN' clause

Deane,

instead of

SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1',
'value3')

try this:

SELECT my_field FROM my_table
join (
select 'value2' c, 1 n
union all
select 'value1', 2
union all
select 'value3', 3
) t
on my_field = t.c
order by t.n

[Serge Rielau]

Re: Ordering results by order of the "IN' clause

deane.barker@gm ail.com wrote:

Thanks.
>
FYI -- I've learned in the meantime that MySQL has this functionality:
>
ORDER BY FIELD(my_field, 'value2','value 1','value3')
>
Syntactic sugar, to be sure, but still handy.

The densest way to write it in ANSI SQL is this:
SELECT my_field FROM my_table JOIN (VALUES(1, 'value2'),
(2, 'value1'),
(3, 'value3')) AS V(I, val)
ON myfield = val
ORDER BY i

This way you don't need to repeat the values.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference

http://www.ibm.com/software/data/ondemandbusiness/conf2006/

[Alexander Kuznetsov]

Re: Ordering results by order of the "IN' clause

Serge,

What are you doing here? Are you porting from SQL Server to DB2 these
days?