CODEWITHSUNDEEP
mysqlsql-order-by
ashishkumar148
ashishkumar148

Reputation: 1005

Custom order by in mysql

I have a table in which there are 3 fields 

+------+------+------+
| name | type | rank |
+------+------+------+
| abc  | A    | 1    |
| def  | B    | 1    |
| ghi  | A    | 2    |
| jkl  | C    | null |
| mno  | B    | 2    |
| pqr  | C    | 1    |
+------+------+------+

How to sort this table first by type (A then B then C) then by rank in ascending order?So that rank with null comes after rank> 0?

Upvotes: 0

Views: 76

Answers (1)

Hart CO
Hart CO

Reputation: 34784

You can use CASE expressions in the ORDER BY: 

SELECT *
FROM YourTable
ORDER BY type
        ,CASE WHEN rank IS NULL THEN 1 ELSE 0 END
        ,rank

Demo: SQL Fiddle

You could also use: 

SELECT *
FROM YourTable
ORDER BY type
        ,COALESCE(rank,999999)

But then you have to pick a number the field can never be higher than, the first method eliminates that guesswork.

Upvotes: 1

Related Questions