CODEWITHSUNDEEP
sql-serverselectsql-order-bycase
Flying Turtle
Flying Turtle

Reputation: 364

Complex order by

I have an order by in a mssql query that needs to be ordered in a complex way. I could solve this using a cursor but it is not a optimal solution.

My select returns a table with user entry and exit, time of entry, and time of exit and I need to sort the earliest entry followed by his exit, then second earliest entry followed by that persons exists, etc. so for example

date---- user ---- action (1 being entry 2 being exit)

0622 ---- 4 --------- 1

0627 ---- 4 --------- 2

0623 ---- 1 --------- 1

0624 ---- 1 --------- 2

0624 ---- 3 --------- 1

0630 ---- 3 --------- 2

0701 ---- 4 --------- 1

0703 ---- 4 --------- 2

I thought about using case where on the order by but I do not know how to have this result.

Thank you for the help,

Upvotes: 0

Views: 125

Answers (1)

Michael
Michael

Reputation: 658

Here is my solution:

select
    your_table.[date],
    your_table.[user],
    your_table.[action]
from your_table
order by
    (case when your_table.[action]=1 then your_table.[date] else (select max(t.[date]) from your_table t where t.[action]=1 and t.[user]=your_table.[user] and t.[date]<=your_table.[date]) end),
    your_table.[user],
    your_table.[action];

Upvotes: 1

Related Questions