How to order by column with non-null values first in sql

Question

I need to write a sql statement to select all users ordered by lastname, firstname. This is the part I know how to do :) What I don't know how to do is to order by non-null values first. Right now I get this:

null, null
null, null
p1Last, p1First
p2Last, p2First

etc

I need to get:

p1Last, p1First
p2Last, p2First
null, null
null, null

Any thoughts?

SQLMenace · Accepted Answer · 2010-05-11 00:42:48Z

36

See Sort Values Ascending But NULLS Last

basically

SELECT *
    FROM @Temp
    ORDER BY CASE WHEN LastName IS NULL THEN 1 ELSE 0 END, LastName

answered May 11, 2010 at 0:42

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Remus Rusanu · Accepted Answer · 2010-05-11 00:38:23Z

12

ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 0 END, name;

answered May 11, 2010 at 0:38

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Comments

Alexandr · Accepted Answer · 2020-03-10 15:26:59Z

0

Nowadays the
IIF ( boolean_expression, true_value, false_value )

will suit too.

ORDER BY IIF(name IS NULL, 1, 0), name

answered Mar 10, 2020 at 15:26

Alexandr

1,7141 gold badge17 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to order by column with non-null values first in sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related