CODEWITHSUNDEEP
sql
user157195
user157195

Reputation:

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 of (a,b,c...). is this possible?

Upvotes: 2

Views: 166

Answers (4)

luke
luke

Reputation: 14788

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.

Upvotes: 0

sevennineteen
sevennineteen

Reputation: 1202

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);

Upvotes: 0

2ndkauboy
2ndkauboy

Reputation: 9397

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

Upvotes: 3

phatmanace
phatmanace

Reputation: 5031

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

Upvotes: 0

Related Questions