Friday, April 22, 2011

SQL Server: Sorting Data in Ascending Order but NULL at the End


Sometime a very simple task becomes challenging for a developer. A friend of mine, who is a talented database developer, asked me that “He needs to sort his query output rows (as we normally do) but he need to push NULL values at the end”.  To be very frank, first I laughed at his question as I thought it’s a simple ORDER BY clause case, but when I tried it, I failed in first attempt ;).
Anyhow, here is a simple solution of sorting any column in ascending order but with NULL at the end.
-- Create temporary
CREATE TABLE #TestSort (RecID INT, RecDesc VARCHAR(50),RecDate DATETIME)
-- Insert rows
INSERT INTO #TestSort
SELECT 1,'FirstRec','2001-07-01 00:00:00'
UNION ALL
SELECT 2,NULL,'2001-08-01 00:00:00'
UNION ALL
SELECT NULL,'ThirdRec',NULL
UNION ALL
SELECT 0,'ForthRec',NULL
UNION ALL
SELECT 5,NULL,'2001-11-01 00:00:00'
GO
---- Sort by INT column "RecID"
SELECT * FROM #TestSort
ORDER BY CASE  WHEN RecID IS NULL  THEN 1 ELSE 0 END,RecID
--- Sort by VARCHAR column "RecDesc"
SELECT * FROM #TestSort
ORDER BY CASE  WHEN RecDesc IS NULL  THEN 1 ELSE 0 END,RecDesc
--- Sort by DATETIME column "RecDate"
SELECT * FROM #TestSort
ORDER BY CASE  WHEN RecDate IS NULL  THEN 1 ELSE 0 END,RecDate
 
-- Drop table when not required
DROP TABLE  #testsort


4 comments:

  1. order by case when isnull(vw.TradeShow,'')='' then 'z' else vw.TradeShow end,ClientName

    ReplyDelete
  2. You want to be careful using a default value, Anon, since that introduces a failure point wherein the default value selected by a developer turns out not to be the absolutely lowest/highest ranked possibility. Better to go with the author's technique of using an explicit first parameter specifically for the null case.

    ReplyDelete
  3. I have always used:

    order by insull(recid,100000)

    where the replacement value is a value that I am sure to be last in a normal order by

    Regards
    Morten

    ReplyDelete

All suggestions are welcome