I have a datetime column in a T-SQL table that I need to sort desc. However, when I do this the NULL values for that field will show up at the bottom. I need them to be at the top of the list, while still having everything else sorted in descending order.

example of query:

SELECT completed FROM table
ORDER BY completed DESC
Re: Sort Null values at top 80 80

add an extra column to the select

SELECT completed, completed IS NULL AS isnull
from table
order by isnull desc, completed desc
Re: Sort Null values at top 80 80

Sadly, when I attempt to run that query I get a message stating: Incorrect syntax near the keyword 'IS'.
I tried a few variations of this to no avail, as well.

Re: Sort Null values at top 80 80

my bad, used my sql
here is the correct way

SELECT completed, ISNULL(completed, NULL) AS 'isnull'
from table
order by isnull desc, completed desc

the order by might need to be changed, based upon the column type of completed, but the select works

Re: Sort Null values at top 80 80

I appreciate all the help dickersonka, but the NULL fields are still at the bottom of the sorted list... I'm sure I'm missing something obvious here.

Re: Sort Null values at top 80 80

Change

order by isnull desc

to

order by isnull
Re: Sort Null values at top 80 80

That certainly got me a lot closer. However, things are ordered as if I had done this query:

select completed
from table
order by completed

Rather than one that is ordered by desc, with NULL values listed first.

Re: Sort Null values at top 80 80

Try this one:

select completed
  from table
  order by case when completed is null then 0 else 1 end, completed desc
Re: Sort Null values at top 80 80

Thank you both for the help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.