SQL query trick

Question

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?

What RDBMS? I did see a way posted to do something similar in MySQL — Martin Smith, Jun 08 '10 at 22:40

score 3 · Answer 1 · edited May 23 '17 at 11:47

3

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

edited May 23 '17 at 11:47

Community

1
1

answered Jun 08 '10 at 22:38

2ndkauboy

9,302
3
31
65

I believe that that solution is specific to mysql, which could pose a problem. – luke Jun 08 '10 at 22:48
As he didn't told use which RDBMS he uses, I quessed that he uses MySQL. If not, it will be easier to find a solution by using the MySQL function fields as a reference for his searches. – 2ndkauboy Jun 08 '10 at 22:53

phatmanace · Answer 2 · 2010-06-08T22:32:59.740

0

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

edited Jun 08 '10 at 22:32

answered Jun 08 '10 at 22:26

phatmanace

4,671
3
24
29

1

because the list is in arbitrary order – Jun 08 '10 at 22:28
As this does not answer the question I would suggest to comment this instead – Oskar Kjellin Jun 08 '10 at 22:29

score 0 · Answer 3 · answered Jun 08 '10 at 22:46

0

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

answered Jun 08 '10 at 22:46

sevennineteen

1,182
8
14

score 0 · Answer 4 · answered Jun 08 '10 at 22:46

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.

SQL query trick

4 Answers4