CODEWITHSUNDEEP
mysqlsql-order-by
kari.patila
kari.patila

Reputation: 1079

Returning query results in predefined order

Is it possible to do a SELECT statement with a predetermined order, ie. selecting IDs 7,2,5,9 and 8 and returning them in that order, based on nothing more than the ID field?

Both these statements return them in the same order: 

SELECT id FROM table WHERE id in (7,2,5,9,8) 

SELECT id FROM table WHERE id in (8,2,5,9,7)

Upvotes: 16

Views: 8800

Answers (11)

Matthias Winkelmann
Matthias Winkelmann

Reputation: 16394

Your best bet is:

ORDER BY FIELD(ID,7,2,4,5,8)

...but it's still ugly.

Upvotes: 3

Nathan
Nathan

Reputation:

One Oracle solution is:

SELECT id FROM table WHERE id in (7,2,5,9,8)
ORDER BY DECODE(id,7,1,2,2,5,3,9,4,8,5,6);

This assigns an order number to each ID. Works OK for a small set of values.

Upvotes: 0

ConroyP
ConroyP

Reputation: 41906

I didn't think this was possible, but found a blog entry here that seems to do the type of thing you're after:

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"7,2,5,9,8");

will give different results to

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"8,2,5,9,7");

FIND_IN_SET returns the position of id in the second argument given to it, so for the first case above, id of 7 is at position 1 in the set, 2 at 2 and so on - mysql internally works out something like

id | FIND_IN_SET
---|-----------
7  | 1
2  | 2
5  | 3

then orders by the results of FIND_IN_SET.

Upvotes: 37

kari.patila
kari.patila

Reputation: 1079

You get answers fast around here, don't you…

The reason I'm asking this is that it's the only way I can think of to avoid sorting a complex multidimensional array. I'm not saying it would be difficult to sort, but if there were a simpler way to do it with straight sql, then why not.

Upvotes: 0

Chris Vest
Chris Vest

Reputation: 8672

It's hacky (and probably slow), but you can get the effect with UNION ALL:

SELECT id FROM table WHERE id = 7
UNION ALL SELECT id FROM table WHERE id = 2
UNION ALL SELECT id FROM table WHERE id = 5
UNION ALL SELECT id FROM table WHERE id = 9
UNION ALL SELECT id FROM table WHERE id = 8;

Edit: Other people mentioned the find_in_set function which is documented here.

Upvotes: 0

Dan
Dan

Reputation: 63390

Erm, not really. Closest you can get is probably:

SELECT * FROM table WHERE id IN (3, 2, 1, 4) ORDER BY id=4, id=1, id=2, id=3

But you probably don't want that :)

It's hard to give you any more specific advice without more information about what's in the tables.

Upvotes: 0

Carra
Carra

Reputation: 17964

Best I can think of is adding a second Column orderColumn:

7 1
2 2
5 3
9 4 
8 5

And then just do a ORDER BY orderColumn

Upvotes: -1

Brian
Brian

Reputation: 5956

You may need to create a temp table with an autonumber field and insert into it in the desired order. Then sort on the new autonumber field.

Upvotes: 0

Jonathan Tran
Jonathan Tran

Reputation: 15276

Could you include a case expression that maps your IDs 7,2,5,... to the ordinals 1,2,3,... and then order by that expression?

Upvotes: 1

JosephStyons
JosephStyons

Reputation: 58685

This works in Oracle. Can you do something similar in MySql?

SELECT ID_FIELD
FROM SOME_TABLE
WHERE ID_FIELD IN(11,10,14,12,13)
ORDER BY
  CASE WHEN ID_FIELD = 11 THEN 0
       WHEN ID_FIELD = 10 THEN 1
       WHEN ID_FIELD = 14 THEN 2
       WHEN ID_FIELD = 12 THEN 3
       WHEN ID_FIELD = 13 THEN 4
  END

Upvotes: 0

Teifion
Teifion

Reputation: 110969

All ordering is done by the ORDER BY keywords, you can only however sort ascending and descending. If you are using a language such as PHP you can then sort them accordingly using some code but I do not believe it is possible with MySQL alone.

Upvotes: 0

Related Questions