ORDER BY with Case-Statement DESC
Solution 1
SELECT columns FROM tables
WHERE condition
ORDER BY
case when Table1.Col1 IS NULL then 0 else 1 end ASC
,case when Table1.Col1 IS NULL then Table2.Col2 else Table1.Col1 end DESC
Solution 2
This should work - just make the first column 0 or 1 based on whether it's null or not:
SELECT columns FROM tables WHERE condition
ORDER BY
case
when Table1.Col1 IS NULL then 0
else 1
end,
case
when Table1.Col1 IS NULL then Table1.Col2
else Table1.Col1
end
Tim Schmelter
ASP.NET and Windows-Developer (C#/VB.NET, ASP.NET/ MVC, Javascript, etc.) Database Admin (primarily SQL-Server and SSAS) I've been programming professionally for more than 10 years, most recently with a focus on C#. Currently working for a company providing banks with real-time financial market and stock exchange information and developing their websites. legendary Legendary badge bearer #109 asp.net ASP.NET gold badge bearer #12 sql-server SQL-Server gold badge bearer #33 c# C# gold badge bearer #143 vb.net VB.NET gold badge bearer #8 .net .NET gold badge bearer #60 epic Epic badge bearer #268 .net .NET silver badge bearer #158 vb.net VB.NET silver badge bearer #14
Updated on June 12, 2022Comments
-
Tim Schmelter almost 2 years
- How to
ORDER BY
with aCASE
-Statement- first group: null values in date-column
Col1
sorted by date-columnCol2 DESC
- second group: not-null values in date-column-
Col1
sorted byCol1 DESC
- first group: null values in date-column
I've tried following:
SELECT columns FROM tables WHERE condition ORDER BY case when Table1.Col1 IS NULL then 0 end, Table2.Col2 DESC, case when Table1.Col1 IS NOT NULL then 1 end, Table1.Col1 DESC
But the sort order is wrong, the NOT NULL values are first(sorted by Col2 instead of Col1). I think i've missed a detail.
- How to
-
Tim Schmelter almost 13 yearsThanks, now the null-values are in front but the not-null values are not ordered by
Col1 desc
butCol2 desc
. -
Petar Ivanov almost 13 yearsjust fixed - sorry, I misunderstood the requirement at first look
-
nl-x over 8 years
if(Cond,TrueVal,FalseVal)
would be more readable here than usingcase when Cond then TrueVal else FalseVal end