MS SQL RSS MS SQL RSS

Sort Null values at top

Please support our MS SQL advertiser: Intel Parallel Studio Home
Thread Solved

•
•
Join Date: Jan 2008
Posts: 29
Reputation: J'Tok is an unknown quantity at this point 
Solved Threads: 0
J'Tok's Avatar
J'Tok J'Tok is offline Offline
Light Poster

Sort Null values at top

 
0
  #1
Aug 29th, 2008
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) 
  1. SELECT completed FROM TABLE
  2. ORDER BY completed DESC
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Sort Null values at top

 
0
  #2
Aug 29th, 2008
add an extra column to the select

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT completed, completed IS NULL AS isnull
  2. FROM TABLE
  3. ORDER BY isnull DESC, completed DESC
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 29
Reputation: J'Tok is an unknown quantity at this point 
Solved Threads: 0
J'Tok's Avatar
J'Tok J'Tok is offline Offline
Light Poster

Re: Sort Null values at top

 
0
  #3
Sep 3rd, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Sort Null values at top

 
0
  #4
Sep 3rd, 2008
my bad, used my sql
here is the correct way

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT completed, ISNULL(completed, NULL) AS 'isnull'
  2. FROM TABLE
  3. 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
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 29
Reputation: J'Tok is an unknown quantity at this point 
Solved Threads: 0
J'Tok's Avatar
J'Tok J'Tok is offline Offline
Light Poster

Re: Sort Null values at top

 
0
  #5
Sep 5th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,160
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 137
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Sort Null values at top

 
0
  #6
Sep 5th, 2008
Change
MS SQL Syntax (Toggle Plain Text) 
  1. ORDER BY isnull DESC

to

MS SQL Syntax (Toggle Plain Text) 
  1. ORDER BY isnull
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 29
Reputation: J'Tok is an unknown quantity at this point 
Solved Threads: 0
J'Tok's Avatar
J'Tok J'Tok is offline Offline
Light Poster

Re: Sort Null values at top

 
0
  #7
Sep 5th, 2008
That certainly got me a lot closer. However, things are ordered as if I had done this query:


MS SQL Syntax (Toggle Plain Text) 
  1. SELECT completed
  2. FROM TABLE
  3. 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
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 42
Reputation: huangzhi is an unknown quantity at this point 
Solved Threads: 13
huangzhi huangzhi is offline Offline
Light Poster

Re: Sort Null values at top

 
0
  #8
Sep 6th, 2008
Try this one:

MS SQL Syntax (Toggle Plain Text) 
  1. SELECT completed
  2.   FROM TABLE
  3.   ORDER BY case when completed IS NULL then 0 else 1 end, completed DESC
Hence Wijaya
www.ex-Soft.tk
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 29
Reputation: J'Tok is an unknown quantity at this point 
Solved Threads: 0
J'Tok's Avatar
J'Tok J'Tok is offline Offline
Light Poster

Re: Sort Null values at top

 
0
  #9
Sep 6th, 2008
Thank you both for the help.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop maximum microsoft ms multithreading news number permission query result server sets sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC