SQL Order By on multiple columns containing Nulls

Question

I have a table containing multiple columns, and I want to order by three of the columns within the table. I want to order by lot, then unit, and lastly number. Lot and Unit can contain nulls (need them listed at the bottom) whereas Number column does not contain Null. I've tried a bunch of case statements but its not giving the desired result. - Order by ... Nulls last is not an option.

Lot	Unit	Number
Null	Null	500
Null	Null	425
Null	Null	424
Null	7	419
Null	9	450
25	Null	475
22	Null	486
22	Null	485
19	7	465
19	9	432

Desired result:

Lot	Unit	Number
19	7	465
19	9	432
22	Null	485
22	Null	486
25	Null	475
Null	7	419
Null	9	450
Null	Null	424
Null	Null	425
Null	Null	500

this was addressed here, https://stackoverflow.com/questions/1498648/sql-how-to-make-null-values-come-last-when-sorting-ascending check it you find multiple answers — Mohamed Hendawy, Apr 08 '21 at 23:16

score 1 · Accepted Answer · answered Apr 08 '21 at 21:21

1

you can do order by checking the nulls:

SELECT *
FROM yourtable
ORDER BY CASE WHEN lot IS NULL THEN 1 ELSE 0 END 
         , lot 
         , CASE WHEN unit IS NULL THEN 1 ELSE 0 END  
         , unit 
         , number

answered Apr 08 '21 at 21:21

eshirvana

23,227
3
22
38

score 1 · Answer 2 · answered Apr 08 '21 at 22:59

You have tagged your question SQL, but not mentioned the database. Standard SQL has a very convenient way to do what you want:

order by Lot nulls last, Unit nulls last, Number

Not all databases support nulls last and nulls first, but this is the standard SQL solution.

SQL Order By on multiple columns containing Nulls

2 Answers2