Maintain WHERE Order In SQL Select

Question

Is it possible to maintain the order of the WHERE clause when doing a SELECT for specific records?

For instance, given the following SELECT statement:

SELECT [RecSeq] FROM [MyData] WHERE
[RecSeq]=3 OR [RecSeq]=2 OR [RecSeq]=1 OR [RecSeq]=21 OR [RecSeq]=20 OR 
[RecSeq]=19 OR [RecSeq]=110 OR [RecSeq]=109 OR [RecSeq]=108 OR 
[RecSeq]=53 OR [RecSeq]=52 OR [RecSeq]=51;

I'd like the results to come back as:

3
2
1
21
20
19
110
109
108
53
53
51

However, what I get back isn't in any particular order. Currently I have a loop that calls the SELECT statement for each record required. This could range anywhere from 1 to 700,000 times. Needless to say the performance isn't the best.

Any solutions or am I stuck in the loop?

Answer 1

You need the ORDER BY FIELD clause.

SELECT RecSeq From MyData WHERE RecSeq IN (3, 2, 1, 21, 20, 19, 110, 109, 108, 53, 52, 51)
ORDER BY FIELD (RecSeq, 3, 2, 1, 21, 20, 19, 110, 109, 108, 53, 52, 51);

You don't say what database system you are using - I know this works in MySQL.

Answer 2

WHERE clause cannot specify your output order.

You will have to sort your results using an "order by".

If you absolutely need this order, try a 'pseudo-column' , or fake column with a union clause (performance warning here).

select 0 as my_fake_column, blah_columns from table where recseq = 3
UNION
select 1,  blah_columns from table where recseq = 2
UNION
select 2,  blah_columns from table where recseq = 1
UNION
select 3,  blah_columns from table where recseq = 21
order by my_fake_column

The above will deliver the results in your specific order 3,2,1,21.

As the other poster said, adding a column could be an option.

Answer 3

There is exactly one way to reliable enforce an ordering of the results of a sql statement: use an order by clause. I don't know if it is standard sql, but in oracle you could do something like this:

select ... from ...
where recseq in ( 3, 2, 1, 21, 20, 19, 110, 109, 108, 53, 53, 51)
order by decode(recseq 3,1, 2,2, 1,3, 21,4, 20,5, 19,6, 110,7, 109,8, 108,9, 53,10, 53,11, 51,12,13)

Answer 4

Ya there is a way, although, some might consider it a hack. Also, I want to point out that you can/should use the IN function instead of the giant conditional statement.

SELECT [RecSeq] 
  FROM [MyData]
 WHERE [RecSeq] in (3,2,1,21,20,19,110,109,108,53,52,51)
ORDER BY DECODE (recseq 3,1, 2,2, 1,3, 21,4,......)

Answer 5

You could try using a UNION. Something like:

SELECT [RecSeq], 1 FROM [MyData] WHERE [RecSeq]=3
UNION
SELECT [RecSeq], 2 FROM [MyData] WHERE [RecSeq]=2
UNION
SELECT [RecSeq], 3 FROM [MyData] WHERE [RecSeq]=1
*etc...*
ORDER BY 2

Answer 6

You can use a derived table for filtering and sorting like this

SELECT  t.RecSeq
FROM    MyData t
JOIN    (
        SELECT 3, 1 UNION ALL
        SELECT 2, 2 UNION ALL
        SELECT 1, 3 UNION ALL
        SELECT 21, 4 UNION ALL
        SELECT 20, 5 UNION ALL
        SELECT 19, 6
        ...
        ) f(RecSeq, SortKey)
ON      t.RecSeq = f.RecSeq
ORDER BY f.SortKey