Returning NULL's at the end of a recordset

• dynamic

  Grasshopper

  Points: 11

  More actions

  November 6, 2001 at 9:22 am

  #77807

  I have just stumbled upon a (TSQL language supported) solution that I have been in search of for a LONG time and wanted to share.

  How many times have you wanted to use ORDER BY ASC on a field that can contain NULL's and did not want the NULL's returned at the top of the recordset?

  I decided to try and use a CASE w/in the ORDER BY clause and to my amazement it was supported.

  Now you can write code like this :

  SELECT * from systypes

  ORDER BY CASE WHEN prec IS NULL THEN 2 ELSE 1 END, prec

  Anyways, if you guys knew this already, then more power to you...

• Paul Ibison

  SSCertifiable

  Points: 5303

  More actions

  November 6, 2001 at 9:48 am

  #419729

  I for one haven't seen this before. Previously I'd created a dummy column and used a case statement to populate it in the select, then used ordered by this dummy column. My method worked, but yours is simpler and returns less data.

  Paul Ibison

  email: Paul.Ibison@btinternet.com

  
  Paul Ibison
  Paul.Ibison@replicationanswers.com

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738160

  More actions

  November 6, 2001 at 10:56 am

  #419740

  What a great T-SQL trick.

  Thanks for sharing!!

  Steve Jones

  steve@dkranch.net

• nigelrivett

  SSCertifiable

  Points: 5472

  More actions

  November 6, 2001 at 1:54 pm

  #419750

  You can also swap the order (the literal just has to be the same case as the field).

  order by case when @dir = 'up' then 1 else prec end desc, prec asc

  You can use the same technique for ommitting fields from the sort.

  Unfortunately you can't include the direction inside a case statement (unless it's changed lately).

  a common use is to return recs from a event log.

  order by case when status = 'failed' then 1 when status = 'warning' then 2 when 'success' then 3 end

  
  Cursors never.
  DTS - only when needed and never to control.