Ad:

MS SQL Discussion Thread
Marked Solved
Views: 6539

Aug 29th, 2008

Sort Null values at top

Expand Post »

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:

MS SQL Syntax (Toggle Plain Text)
SELECT completed FROM TABLE
ORDER BY completed DESC
SELECT completed FROM table
ORDER BY completed DESC

Similar Threads

Please check my code below in Java
Open In New Window Php in PHP

J'Tok

Reputation Points: 22

Solved Threads: 0

Light Poster

Offline

39 posts
since Jan 2008

Permalink

Aug 29th, 2008

Re: Sort Null values at top

add an extra column to the select

MS SQL Syntax (Toggle Plain Text)
SELECT completed, completed IS NULL AS isnull
FROM TABLE
ORDER BY isnull DESC, completed DESC
SELECT completed, completed IS NULL AS isnull
from table
order by isnull desc, completed desc

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 3rd, 2008

Re: Sort Null values at top

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.

J'Tok

Reputation Points: 22

Solved Threads: 0

Light Poster

Offline

39 posts
since Jan 2008

Permalink

Sep 3rd, 2008

Re: Sort Null values at top

my bad, used my sql
here is the correct way

MS SQL Syntax (Toggle Plain Text)
SELECT completed, ISNULL(completed, NULL) AS 'isnull'
FROM TABLE
ORDER BY isnull DESC, completed DESC
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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 5th, 2008

Re: Sort Null values at top

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.

J'Tok

Reputation Points: 22

Solved Threads: 0

Light Poster

Offline

39 posts
since Jan 2008

Permalink

Sep 5th, 2008

Re: Sort Null values at top

Change

MS SQL Syntax (Toggle Plain Text)
ORDER BY isnull DESC
order by isnull desc

MS SQL Syntax (Toggle Plain Text)

ORDER BY isnull

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Sep 5th, 2008

Re: Sort Null values at top

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

MS SQL Syntax (Toggle Plain Text)
SELECT completed
FROM TABLE
ORDER BY completed
select completed
from table
order by completed

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

Last edited by J'Tok; Sep 5th, 2008 at 3:47 pm. Reason: Clarification

J'Tok

Reputation Points: 22

Solved Threads: 0

Light Poster

Offline

39 posts
since Jan 2008

Permalink

Sep 6th, 2008

Re: Sort Null values at top

Try this one:

MS SQL Syntax (Toggle Plain Text)
SELECT completed
  FROM TABLE
  ORDER BY case when completed IS NULL then 0 else 1 end, completed DESC
select completed
  from table
  order by case when completed is null then 0 else 1 end, completed desc

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Sep 6th, 2008

Re: Sort Null values at top

Thank you both for the help.

J'Tok

Reputation Points: 22

Solved Threads: 0

Light Poster

Offline

39 posts
since Jan 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL - Select semi-duplicate rows?

Next Thread in MS SQL Forum Timeline: Interface design - SQLSERVER 2005 to ORACLE Data updation

DaniWeb Message

Cancel Changes

User Name
Password