CODEWITHSUNDEEP
sql-server-2005t-sqlsql-order-by
Tim Schmelter
Tim Schmelter

Reputation: 460058

ORDER BY with Case-Statement DESC

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.

Upvotes: 7

Views: 10521

Answers (2)

Petar Ivanov
Petar Ivanov

Reputation: 93020

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

Upvotes: 4

Johan
Johan

Reputation: 76537

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

Upvotes: 7

Related Questions