How to ORDER BY based on condition? SQL Server

Question

I have a table below for example:

ID          NAME           Company          StartDate        EndDate        Status     
--------------------------------------------------------------------------------------
50          Bob            ABC              11-15-2016       02-05-2017     Former  
50          Bob            XYZ              05-06-2014       05-06-2015     Former
50          Bob            EFG              06-10-2019       NULL           Active
50          Bob            LMN              07-05-2019       NULL           Active

I am trying to list their 3 most recent jobs in descending order, so their "Active" job will be first, but if they have 2 "Active" jobs like the example above, I only want to pull the "Active" job with the most recent start date, OR if both of the "Active" jobs have the same start date, then just randomly choose one of them, and skip the other Active, then move on to the their most recent former jobs. The "Active" jobs will always have a NULL EndDate value.

So the result set I am looking for is like below:

ID          NAME           Company          StartDate        EndDate        Status        rn     
----------------------------------------------------------------------------------------------
50          Bob            LMN              07-05-2019       NULL           Active         1 
50          Bob            ABC              11-15-2016       02-05-2017     Former         2
50          Bob            XYZ              05-06-2014       05-06-2015     Former         3

I have tried the below code, but it isn't able to check for two Active job status and give them a row number properly. I'd like for only one of the Active jobs to be rn = 1 based on the conditions stated above, and rn = 2 and rn = 3 will be the last 2 "Former" jobs:

  select *, row_number() over (partition by ID, NAME order by CASE WHEN Status = 'Active' AND 
  EndDate is NULL THEN Status END desc, EndDate desc
  ) rn
  from employment

Could they have *more* that two active jobs? If so, presumably still just one to appear in the result? — Damien_The_Unbeliever, Sep 03 '20 at 14:50

score 2 · Accepted Answer · answered Sep 03 '20 at 14:58

2

You can do this with window functions. Assuming that "Active" and "Former" are the only two possible status values, you could do:

select *
from (
    select t.*, 
        row_number() over (partition by id, status order by startDate desc) rn
    from mytable t
) t
where 
    (status = 'Active' and rn = 1)
    or (status <> 'Active' and rn <= 2)

If there may be other statuses, then you would change the window function to:

row_number() over (
    partition by id, case when status = 'Active' then 1 else 0 end 
    order by startDate desc
) rn

Finally, if you want to use startDate to order "Active" recorod and endDate for others:

row_number() over (
    partition by id, case when status = 'Active' then 1 else 0 end 
    order by case when status = 'Active' then startDate else endDate end desc
) rn

answered Sep 03 '20 at 14:58

GMB

216,147
25
84
135

Yes there is also a "Retired" status which I would also want included with the same criteria as "Former". Also, some "Former" or "Retired" jobs may have a NULL StartDate as well, but will always have an EndDate – jwalls91 Sep 03 '20 at 15:23
@jwalls91: the last expression appears to do what you want. – GMB Sep 03 '20 at 15:35
When I tried that last expression, there seems to be an issue with Former jobs with a NULL StartDate. It is giving the Active as rn = 1 which is correct, but also it is giving one of the "Former" jobs that has a NULL StartDate a rn = 1 as well. So I guess I need to find a way to account for "Active" as rn = 1, and also account for "Former" or "Retired" which will always have an EndDate, but may have a NULL StartDate, and sort these "Former" or "Retired" by most recent. And if they have no "Active", then just simply sort by their Former/Retired jobs in desc order – jwalls91 Sep 03 '20 at 16:05
It looks like after I edited the part "case when status = 'Active' then 1 else 0" to "case when status = 'Active' then 0 else 1", this seemed to now order them properly, even though one of the Former is still rn = 1, which I'd need it to be rn = 2 and rn = 3 for the former/retired. – jwalls91 Sep 03 '20 at 16:15
@jwalls91: you can adjust the row numbers in the outer query, like `case when status = 'Active' then rn else rn + 1 end as rn` – GMB Sep 03 '20 at 16:18
1

This seems to work, basically created another column with the proper numbering. Thank you! – jwalls91 Sep 03 '20 at 16:42

iceblade · Answer 2 · 2020-09-03T15:04:38.603

You can use CTEs to get the latest Active job and the rn for the Former jobs, then UNION both:

;with cte1 as
(
select Id, Name, C.Company, C.StartDate, C.EndDate, C.Status, rn = 1
From employment E
Outer Apply
(
    Select top 1    E2.Company,
                    E2.StartDate,
                    E2.EndDate,
                    E2.Status
    From employment E2
    where E.Id = E2.Id and E.Name = E2.Name
    and E2.Status = 'Active'
    order by E2.StartDate desc
) C
group by E.Id, E.Name, C.Company, C.StartDate, C.EndDate, C.Status
),
cte2 as
(
  select *, 1 + row_number() over (partition by ID, NAME order by StartDate desc
  ) rn
  from employment
  where Status = 'Former'
)   
    select * from cte1
   union
    select * from cte2
    where rn <= 3
   order by Id asc, Name asc, StartDate desc

Output:

How to ORDER BY based on condition? SQL Server

2 Answers2