Error in Union trying to sort null at end in sql server

Question

Hi,

I am trying to sort my database in some particular order, but I want all null's at the end, so I am selecting all values with not null, and then selecting all values WITH null, and trying to join them with Union.. like:

SELECT * FROM preferences WHERE preferenceOrder IS NOT NULL 
                               ORDER BY preferenceOrder ASC
UNION

SELECT * FROM preferences WHERE preferenceOrder IS NULL 
                               ORDER BY preferences ASC

but the server throws an error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'union'.

I cant find out what the error is though.. what is wrong with the sentence above?

thanks!

Answer 1

i think you should order by (case when preferenceOrder is null then 0 else 1 end), preferenceOrder instead of this union.