CODEWITHSUNDEEP
sqlsql-servert-sql
NickG
NickG

Reputation: 9812

SQL: How can I order null and empty entries to the front in an orderby?

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 ''

Upvotes: 9

Views: 10518

Answers (4)

Torres
Torres

Reputation: 5400

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

Upvotes: 0

mwigdahl
mwigdahl

Reputation: 16578

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

Upvotes: 2

aF.
aF.

Reputation: 66697

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.

Upvotes: 2

JNK
JNK

Reputation: 65157

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

Upvotes: 29

Related Questions