Sort by specific order in MySQL query

If you want to sort the list by a certain order, such as a list of numbers from the beginning of 4,5,1, and then in ascending order, you can create a sort order by IF statement rule with additional columns for sorting:

1
2
3
4
5
6
7
SELECT id,
-- additional columns for sorting
IF(id=4, 1, 0) c1, IF(id=5, 1, 0) c2, IF(id=1, 1, 0) c3
FROM test
WHERE id IN(1,2,3,4,5,6,7,8,9,10)
-- now sort by additional columns descending and other ascending
ORDER BY c1 DESC, c2 DESC, c3 DESC, id ASC

Query result:

1
2
3
4
5
6
7
8
9
id  c1  c2  c3
4   1   0   0
5   0   1   0
1   0   0   1
6   0   0   0
7   0   0   0
8   0   0   0
9   0   0   0
10  0   0   0

In this example, the statement condition returns 1 or 0, since 0 is above 1, so do a reverse sort by descending order, then the values ​​in additional columns will be first. MySQL statements can be used immediately in the sorting, then the query is changed as follows:

1
2
3
4
5
SELECT id
FROM test
WHERE id IN(1,2,3,4,5,6,7,8,9,10)
-- sort order by conditions
ORDER BY IF(id=4, 1, 0) DESC, IF(id=5, 1, 0) DESC, IF(id=1, 1, 0) DESC, id ASC

Query result:

1
2
3
4
5
6
7
8
9
id
4
5
1
6
7
8
9
10

Since the IF statement return 0 or 1, it can be replaced by equal operator, so query would be:

1
2
3
4
5
SELECT id
FROM test
WHERE id IN(1,2,3,4,5,6,7,8,9,10)
-- sort order by equal operators
ORDER BY id=4 DESC, id=5 DESC, id=1 DESC, id ASC

Query result:

1
2
3
4
5
6
7
8
9
id
4
5
1
6
7
8
9
10

Instead IF statement can use CASE statement:

1
2
3
4
5
6
7
8
9
10
11
SELECT id
FROM test
WHERE id IN(1,2,3,4,5,6,7,8,9,10)
ORDER BY
-- sort by case
CASE id
    WHEN 4 THEN 1
    WHEN 5 THEN 2
    WHEN 1 THEN 3
    ELSE 4
END ASC

Query result:

1
2
3
4
5
6
7
8
9
id
4
5
1
6
7
8
9
10
Posted on 2012-03-23 by Aleksandras
This entry was posted in Programming and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.