CODEWITHSUNDEEP
sqlsql-servernullsql-order-by
markzzz
markzzz

Reputation: 47945

Is it possible to put NULL records as last results on a ORDER BY?

This is my SQL query :

SELECT     TOP (200) IDA
FROM         Categories
WHERE     (IDA = 72)
ORDER BY Order

but it puts the records with Order NULL before the records with Order as int. Is there a strategy to change this order? Putting the records with NULL at the bottom of the result list.

Upvotes: 0

Views: 944

Answers (2)

Peter Wishart
Peter Wishart

Reputation: 12280

Works for any type:

select top(200) IDA from Categories
order by case when [order] is null then 1 else 0 end, [order]

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

Yes:

SELECT     TOP (200) IDA
FROM         Categories
WHERE     IDA = 72
ORDER BY 
   CASE 
       WHEN [ORDER] IS NULL 
       THEN 0 
       ELSE 1
   END DESC, [Order]

Upvotes: 2

Related Questions