php mysql sort results by order fetched

Question

How can I order the results I get by the order of the ids in the array when using IN statement:

SELECT id,name from table WHERE id IN (23, 33, 55, 2)

I want to get:

+----+---------+
| id | name    |
+----+---------+
| 23 | name1   |
| 33 | name2   |
| 55 | name3   |
|  2 | name4   |
+----+---------+

in this order

Just to clarify, what he's trying to achieve is sorting on the CSV-list of IDs. So for example (23, 33, 55, 2) and (2, 55, 33, 23) will return the IDs in that order. Not just sorting by the id desc/asc. — Niklas Modess
– Niklas Modess, Commented Sep 17, 2012 at 13:15

Raheel Hasan · Accepted Answer · 2012-09-17 13:12:16Z

6

simply add this at the end of your query:

order by field(id, 23, 33, 55, 2)

answered Sep 17, 2012 at 13:12

Raheel Hasan

6,0634 gold badges47 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

knittl · Accepted Answer · 2012-09-17 13:09:37Z

2

You can try to use a JOIN. It is not guaranteed to be returned in this order, but for MySQL this holds true most of the time:

SELECT table.id, table.name
FROM ( 
  SELECT 23 id UNION SELECT 33 id UNION SELECT 55 id UNION SELECT 2
) a
JOIN table
ON a.id = table.id

How is your order determined? If you can provide a sorting-function it gets easy.

answered Sep 17, 2012 at 13:09

knittl

269k59 gold badges339 silver badges405 bronze badges

2 Comments

Fluffeh Over a year ago

+1 This was where I was going to go with it. I am uncertain though if the db would return the rows according to it's own whim in terms of order or follow the union in the subselect?

knittl Over a year ago

@Fluffeh: well, the "own whim" of the union-subselect is the order as-written. And MySQL usually leaves the order as is when joining (although it does not have to, and SQL usually operates on sets – i.e. no deterministic ordering)

Zeritor · Accepted Answer · 2012-09-17 13:10:34Z

2

SELECT id,name from table WHERE id IN (23, 33, 55, 2) ORDER BY FIELD (id, 2, 23, 33, 55)

answered Sep 17, 2012 at 13:10

Zeritor

3131 gold badge2 silver badges8 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-09-17 13:15:09Z

1

You could try something like this:

SELECT t.id,t.name 
from 
    (select 23 as id,1 as rnk union all 
     select 33 as id,2 union all
     select 55 as id,3 union all
     select 2 as id,4 )input
join table t 
on   t.id=input.id
order by input.rnk

edited Sep 17, 2012 at 13:15

answered Sep 17, 2012 at 13:06

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Comments

xception · Accepted Answer · 2012-09-17 13:17:34Z

1

SELECT id,name from table WHERE id IN (23, 33, 55, 2) ORDER BY id = 23 DESC, id = 33 DESC, id = 55 DESC, id = 2 DESC not a database specific solution but still works

edited Sep 17, 2012 at 13:17

answered Sep 17, 2012 at 13:08

xception

4,3171 gold badge20 silver badges29 bronze badges

5 Comments

knittl Over a year ago

Interesting approach :) Also possible: ORDER BY id != 23 (ASC), id != ...

xception Over a year ago

I used it when I needed to order on a weird field combination which would change depending on another field, needless to say it was a very complex query to write

xception Over a year ago

included the answer in case he actually wanted something more complex but tried to give a simplified example and the field approach wouldn't work

knittl Over a year ago

The reason this works is because MySQL converts boolean values to integers for sorting. So false becomes 0 and true becomes 1 (and 0 and 1 have a defined ordering)

xception Over a year ago

I'm almost certain it works in most databases, not just mysql

Collectives™ on Stack Overflow

php mysql sort results by order fetched

5 Answers 5

Comments

2 Comments

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related