Thursday, April 23, 2009

SQL Server Tricks - Order By IN Order

SQL Server Tricks - Order By IN Order



Have you ever wanted to return the rows of select statement in the order of the list you were querying IN? Did that sentence even make sense...

Here's the solutions:

SELECT *
FROM SomeDataTable
where someID in (5,6000,5000,1000,2)
ORDER BY CHARINDEX( ','+Convert(varchar,someID)+',',',5,6000,5000,1000,2,' )




While this is elegant, in some situations (perhaps where indexing is not done) it is costly. Darian Ford, a colleage of mine, found a solution from Kapil Srivastava which solves this problem:

select * from publishers
Order by (CASE City
WHEN 'Paris' THEN 1
WHEN 'Chicago' THEN 2
WHEN 'Boston' THEN 3
WHEN 'New York' THEN 4
WHEN 'Berkeley' THEN 5
WHEN 'Dallas' THEN 6
ELSE 100 END) ASC,City DESC


See eggheadcafe
Kapil's solution

No comments: