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

add an extra column to the select

SELECT completed, completed IS NULL AS isnull
from table
order by isnull desc, completed desc

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.

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

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.

Change

order by isnull desc

to

order by isnull

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.

Try this one:

select completed
  from table
  order by case when completed is null then 0 else 1 end, completed desc

Thank you both for the help.

Be a part of the DaniWeb community

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