CODEWITHSUNDEEP
phpmysql
publikz.com
publikz.com

Reputation: 961

MySQL sort by some list

I have a list of numbers: 7,1,3,2,123,55 (which are the ids of existing records)

I have a mysql table with the colums id and name, where id is an integer primary key. I want to select records from this table, but in a specific order, for example 7,1,3,2,123,55.

Upvotes: 31

Views: 20052

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

Since 1 < 3 < 77 < 123, a simple ORDER BY id would suffice.

If, however, you want to order this way: 77, 3, 123, 1, then you could use function FIELD():

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

If your query matches more rows than you list in FIELD

FIELD returns 0 when a row does not match any of the ids you list, i.e. a number smaller than the numbers returned for listed ids. This means, if your query matches more rows than the ones you list, those rows will appear first. For example:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 77, 3, 123, 1)

In this example, the row with ID 400 will appear first. If you want those rows to appear last, simply reverse the list of IDs and add DESC:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 1, 123, 3, 77) DESC

Upvotes: 74

Related Questions