I have got the following priority list table where Source4 takes priority over 3, 3 over 2, 2 over 1, and so on.
SourceID | SourceDescription |
---|---|
1 | Source1 |
2 | Source2 |
3 | Source3 |
4 | Source4 |
I also have the following table with data (however the table will contain multiple disitinct EventIDs)
EventID | CommencingTime | SourceID |
---|---|---|
12345 | 2021-10-24 11:27:34 | 1 |
12346 | 2021-10-24 11:27:34 | 1 |
12347 | 2021-10-24 11:27:34 | 1 |
12345 | 2021-10-24 12:58:55 | 3 |
12346 | 2021-10-24 12:58:55 | 3 |
12347 | 2021-10-24 12:58:55 | 3 |
12345 | 2021-10-24 10:58:00 | 2 |
12346 | 2021-10-24 10:58:00 | 2 |
12347 | 2021-10-24 10:58:00 | 2 |
How can I get the lowest date from the above based on the priority list.
In this case the correct result set would be:
EventID | CommencingTime | SourceID |
---|---|---|
12345 | 2021-10-24 12:58:55 | 3 |
12346 | 2021-10-24 12:58:55 | 3 |
12347 | 2021-10-24 12:58:55 | 3 |
I have tried doing a MIN OVER PARTITION BY ORDER BY SourceID DESC
, but that just keeps returning 2021-10-24 10:58:00 and ignores the priority SourceID
CodePudding user response:
You may use rank
to achieve this based on your sample shared
select
EventID,CommencingTime,SourceID
from (
select
*,
rank() over (order by SourceID DESC,CommencingTime) as rn
from
mytable
) t
where rn=1