CODEWITHSUNDEEP
sqlnullsql-order-bycase
Riz_K
Riz_K

Reputation: 71

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

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

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

Upvotes: 1

Related Questions