CODEWITHSUNDEEP
sqlsql-serversorting
snumpy
snumpy

Reputation: 2878

Conditionally Order by Ascending or Descending on the Same Column

I need to make a query that is ordered based on a date column with the following rules:

  1. First: NULLs
  2. Then: ORDER BY ASC if Date >= CURRENT_TIMESTAMP
  3. Finally: ORDER BY DESC if Date < CURRENT_TIMESTAMP

I have the following:

SELECT * FROM [Table]
ORDER BY (CASE WHEN [Date] IS NULL THEN 0 ELSE 1 END),
    (CASE WHEN [Date] >= CAST(CURRENT_TIMESTAMP AS DATE) THEN 0 ELSE 1 END),
    [Date] ASC

But this does not return items older than today's date in descending order. How would I modify my query to account for all three requirements?

Upvotes: 2

Views: 2578

Answers (1)

radar
radar

Reputation: 13425

we can make them into groups and do sorting on the group in the first order by clause.

   SELECT * 
    from test
    order by (case when [Date] is null then 0 
             when [Date] >= getdate() then 1 
             when [Date] < getdate() then 2 
             end ) asc ,
             case when [Date] >= getdate() then [Date] end asc,
             case when [Date] < getdate() then [Date] end desc

Upvotes: 4

Related Questions