returning id in order from query

Question

Say I have the following query

SELECT * 
FROM foo 
WHERE P_KEY IN (5,7,2,6);

When I enter the query, the P_KEY values return the rows in the order of 2,5,6,7. How can I make it so it returns the rows in the order I entered it in the IN clause

This

P_KEY
5
7
2
6

NOT This

P_KEY
2
5
6
7

PLEASE, tag your question with what RDBMS you are using. If it is MySQL I have an exact answer for you — Tyler Benzing, Apr 19 '16 at 20:28
Possible duplicate of [Keep order from 'IN' clause](http://stackoverflow.com/questions/14139931/keep-order-from-in-clause) — Tyler Benzing, Apr 19 '16 at 20:34

score 0 · Answer 1 · answered Apr 19 '16 at 20:27

You can't directly order by the order of items in the IN clause. You'll have to create a temporary table or in-line sub-query to add an order to the values:

SELECT Foo.* FROM FOO
INNER  JOIN
(
   SELECT 5 AS [Key], 1 AS [Order]
   UNION ALL
   SELECT 7 AS [Key], 2 AS [Order]
   UNION ALL
   SELECT 2 AS [Key], 3 AS [Order]
   UNION ALL
   SELECT 6 AS [Key], 4 AS [Order]
) j
ON P_KEY = j.key
ORDER BY j.[Order]

score 0 · Answer 2 · answered Apr 19 '16 at 20:28

0

A simple and not so elegant solution is to use UNION

SELECT * FROM foo WHERE P_KEY =5
UNION
SELECT * FROM foo WHERE P_KEY =7
UNION
SELECT * FROM foo WHERE P_KEY =2
UNION
SELECT * FROM foo WHERE P_KEY =6

answered Apr 19 '16 at 20:28

JustAPup

1,720
12
19

Is (5,7,2,6) something that a user selects, to be passed on as parameters to a stored procedure? If so, you can build the SQL by looping through those values. The proposed action above is more of a cheap and quick way to take care of your temporary problem, but I agree, in the long run you will need something more sophisticated :) – JustAPup Apr 19 '16 at 20:44

returning id in order from query

2 Answers2