SQL Order By on multiple columns containing Nulls

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

Answer

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

Advertisement

Answer