Conditional Order By In SQL Server

Question

I have some difficulties working on a query with conditional order by. Here is a sample data :

|Id | Date-In  | Date-Out |
|1  | 01/01/21 | NULL     |
|2  | 03/01/21 | NULL     |
|3  | 05/01/20 | 11/01/21 |
|3  | 12/01/21 | NULL     |
|4  | 12/12/21 | 15/01/21 |
|5  | 17/01/21 | 21/01/21 |

I want to sort the data like this :

|Id | Date-In  | Date-Out |
|5  | 17/01/21 | 21/01/21 |
|4  | 12/12/20 | 15/01/21 |
|3  | 05/01/21 | 11/01/21 |
|3  | 12/01/21 | NULL     |
|2  | 03/01/21 | NULL     |
|1  | 01/01/21 | NULL     |

When there is a move-out sort DESC
When there is a move-out and a move-in sort move-out DESC, move-in DESC, Id DESC (Bold) Else sort move-in DESC

question from:https://stackoverflow.com/questions/65883914/conditional-order-by-in-sql-server

Answer 1

I tried this solution :

  SELECT  Id
      ,[move_in_date]
      ,[move_out_date]
      ,LAG(move_out_date) OVER(Partition By Id ORDER BY move_in_date) as Lag_out
  FROM MyTable
  ORDER BY isnull(move_out_date,LAG(move_out_date) OVER(Partition By Id ORDER BY move_in_date)) DESC

I don't know if this will work with every scenario i mentioned in my first post