Ordering results by order of the "IN' clause

[deane.barker]

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]

(de**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[deane.barker]

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:

(de**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

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]

de**********@gmail.com wrote:

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.

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/ond...ness/conf2006/

[Alexander Kuznetsov]

Serge,

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