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 temporaryCREATE TABLE #TestSort (RecID INT, RecDesc VARCHAR(50),RecDate DATETIME)-- Insert rowsINSERT INTO #TestSortSELECT 1,'FirstRec','2001-07-01 00:00:00'UNION ALLSELECT 2,NULL,'2001-08-01 00:00:00'UNION ALLSELECT NULL,'ThirdRec',NULLUNION ALLSELECT 0,'ForthRec',NULLUNION ALLSELECT 5,NULL,'2001-11-01 00:00:00'GO---- Sort by INT column "RecID"SELECT * FROM #TestSortORDER BY CASE WHEN RecID IS NULL THEN 1 ELSE 0 END,RecID--- Sort by VARCHAR column "RecDesc"SELECT * FROM #TestSortORDER BY CASE WHEN RecDesc IS NULL THEN 1 ELSE 0 END,RecDesc--- Sort by DATETIME column "RecDate"SELECT * FROM #TestSortORDER BY CASE WHEN RecDate IS NULL THEN 1 ELSE 0 END,RecDate-- Drop table when not requiredDROP TABLE #testsort
order by case when isnull(vw.TradeShow,'')='' then 'z' else vw.TradeShow end,ClientName
ReplyDeleteYou 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.
ReplyDeleteThanks.Easy to under with your example
ReplyDeletehttp://csharpektroncmssql.blogspot.com/2012/06/how-to-sort-data-in-sql.html
I have always used:
ReplyDeleteorder 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