SQL Server conditional Order By with multiple columns

Question

I have a table with thousands of executions that may have one of the following results in the state column:

OK
NOTOK
CANCELED
RUNNING

I need a query where i can sort the results so that the executions running appear on top, and the rest of the results are sorted by start date or ID, according to user preference.

example: Table records:

ID - STATE - START_DATE
1      OK      12:00
2    RUNNING   12:10
3     NOTOK    12:30
4    RUNNING   12:45
5      OK      13:00

Expect result when sorted by START_DATE:

ID - STATE - START_DATE
4   RUNNING    12:45
2   RUNNING    12:10
5     OK       13:00
3    NOTOK     12:30
1     OK       12:00

This is the query i already have (simplified):

SELECT TOP (@ITEMS_PAGE) ID, START_DATE, STATE
FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY
                                  CASE WHEN @ORDER = 'ID&ASC' THEN ID END ASC,
                                  CASE WHEN @ORDER = 'ID&DESC' THEN ID  END DESC,
                                  CASE WHEN @ORDER = 'START_DATE&ASC' THEN START_DATE END ASC,
                                  CASE WHEN @ORDER = 'START_DATE&DESC' THEN START_DATE END DESC,
                                  CASE WHEN @ORDER = 'STATE&ASC' THEN STATE END ASC,
                                  CASE WHEN @ORDER = 'STATE&DESC' THEN STATE END DESC,
                                  CASE WHEN @ORDER IS NULL THEN ID END DESC
                              ) AS ROWNUMBERS, * 
     FROM @TMPPAGING 
     WHERE (@RESULT is null or STATE = @RESULT)
) S
WHERE S.ROWNUMBERS > (@CURRENT_PAGE - 1) * @ITEMS_PAGE

@TMPPAGING contains the records.

Brian Leach · Accepted Answer · 2018-02-08 19:58:04Z

2

You are making this much more complex than it needs to be. Here is your SQL for ordering by start_date:

with inputdata as ( select 1 as id, 'OK' as state, convert(datetime, '2018-01-10 12:00') as start_date
union all select 2 as id, 'RUNNING' as state, convert(datetime, '2018-01-10 12:10') as start_date
union all select 3 as id, 'NOTOK' as state, convert(datetime, '2018-01-10 12:30') as start_date
union all select 2 as id, 'RUNNING' as state, convert(datetime, '2018-01-10 12:45') as start_date
union all select 2 as id, 'OK' as state, convert(datetime, '2018-01-10 13:00') as start_date

) 
select * from inputdata order by case when state='RUNNING' then 1 else 2 end, start_date

This results in:

id  state   start_date
2   RUNNING 2018-01-10 12:10:00.000
2   RUNNING 2018-01-10 12:45:00.000
1   OK      2018-01-10 12:00:00.000
3   NOTOK   2018-01-10 12:30:00.000
2   OK      2018-01-10 13:00:00.000

answered Feb 8, 2018 at 19:58

Brian Leach

2,1111 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Acorian0 Over a year ago

The real case is a bit more complex than the one presented (I have paging, and multiple order columns) but your solution does wonders :) thanks.

Collectives™ on Stack Overflow

SQL Server conditional Order By with multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related