Home > Software engineering >  Get the lowest date out of a set based on priority ID
Get the lowest date out of a set based on priority ID

Time:10-27

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

View working demo here

  • Related