For each ID I would like to get the value with the lowest rank and the second lowest rank (possible ranks: 1 - 7). If an ID has two times the same rank, I want to select the latest, based on the column LastUpdate:
| ID | value | rank | LastUpdate |
|---|---|---|---|
| 1 | a | 1 | 2021-01-19 |
| 1 | b | 2 | 2021-01-19 |
| 1 | c | 3 | 2021-01-19 |
| 2 | d | 3 | 2021-01-19 |
| 2 | e | 3 | 2021-01-18 |
| 2 | f | 4 | 2021-01-18 |
| 3 | g | 2 | 2021-01-19 |
| 3 | h | 7 | 2021-01-19 |
| 3 | i | 7 | 2021-01-20 |
In this case, my desired output is:
| ID | value_lowest_rank | value_second_lowest_rank |
|---|---|---|
| 1 | a | b |
| 2 | d | e |
| 3 | g | i |
CodePudding user response：
Use row_number to number the rows by rank and date, then apply conditional aggregation:
with cte as (
select *, row_number() over (partition by id order by rank, lastupdate desc) as rn
from t
)
select id, max(case when rn = 1 then value end), max(case when rn = 2 then value end)
from cte
group by id