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
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