SQL query trick

I need to do

select * from xxx where name in (a,b,c...);

but I want the result set to be in the order 开发者_C百科of (a,b,c...). is this possible?

I found this question which is looks like your original question: Ordering by the order of values in a SQL IN() clause

ah - I see. you could do something horrendous with a case statement, and then order by that.. you'd effectivley be adding another column to your query to be an "order" that you could then "order by"

its ugly, but if you control the query, and the number in the 'in' clause is low, it could work (beleive an 'in' clause is limited to 255 chars)

e.g "IF name = a then 1 else if name = b then 2"

Failing that, probably best to sort in the client using a similar technique (assuming it was the client that injected the information into the 'in' clause in the first place)

-Ace

The method to do this will be DB-specific.

In Oracle, you could do something like:

SELECT * FROM xxx 
where name in (a,b,c...)
ORDER BY DECODE(name,a,1,b,2,c,3);

IN statements are pretty limited, but you could get a similar effect by joining on a subquery.

here's an example:

SELECT x.* 
FROM xxx as x 
    INNER JOIN ((select a as name, 1 as ord)
                UNION
                (select b as name, 2 as ord)
                UNION
                (select c as name, 3 as ord)) as t
        ON t.name = x.name
ORDER BY t.ord

its pretty ugly, but it should work on just about any sql database. The ord field explicitly allows you to set the ordering of the result. some databases such as SqlServer support a ROWINDEX feature so you may be able to use that to clean it up a bit.