SQL: How can I order null and empty entries to the

问题:

If I have the following entries in my database:

ID Name
1 [null]
2 [empty string]
3 Alpha
4 Bravo
5 Charlie

..then how can I order the rows with names to the front when using ORDER BY?

If I use ORDER BY Name, I get the list above, but I actually want:

3 Alpha
4 Bravo
5 Charlie
1 [null]
2 ''

回答1:

ORDER BY 
    CASE 
    WHEN Name IS NULL THEN 1 
    WHEN Name = ''    THEN 2 
    ELSE 3 
    END DESC, 
    Name ASC

回答2:

You can do it like this:

ORDER BY CASE WHEN Name IS NULL then 3 WHEN Name = '' THEN 2 ELSE 1 END, Name

It will order with by the number in the case first and afterwords by the Name.

回答3:

ORDER BY 
CASE 
    WHEN Name IS NULL THEN 1 
    WHEN Name = '' THEN 2 
    ELSE 3
END DESC,
Name ASC

回答4:

ORDER BY (CASE WHEN Name IS NULL THEN 1 WHEN Name IS NULL THEN 2 ELSE 3 END) DESC