Based on a table like this I want to group by PRODID, then select a row based on a case statement. Case statement being if IND1 = 1 select row, else if IND2 = 1 select row, else select row with max DATE. If multiple indicators within a group are 1 select row with the max date from the group of indicators being 1.
PRODID IND1 IND2 DATE --------------------------------------------------- 1 1 0 4/24/2020 1 0 0 1/2/2020 1 1 0 1/1/2020 2 0 1 4/24/2020 2 0 1 1/1/2020 3 0 0 4/24/2020 3 0 0 1/1/2020 4 1 0 2/2/2020 4 0 1 4/24/2020 4 0 1 4/24/2020
PRODID IND1 IND2 DATE --------------------------------------------------- 1 1 0 4/24/2020 2 0 1 4/24/2020 3 0 0 4/24/2020 4 1 0 2/2/2020
Advertisement
Answer
One method uses row_number():
select t.*
from (select t.*,
row_number() over (partition by prodid
order by (case when ind1 > 0 then 1 else 2 end),
(case when ind1 = 0 and ind2 > 0 then 1 else 2 end),
date desc
) as seqnum
from t
) t
where seqnum = 1;
If you like arithmetic, this can be simplified to:
select t.*
from (select t.*,
row_number() over (partition by prodid
order by ind1 desc, ind2 * (1 - ind1) desc, date desc
) as seqnum
from t
) t
where seqnum = 1;
Note that one trick in putting the logic together is ensuring that the first row is used in the following situation:
1 0 0 2020-01-21 1 1 0 2020-01-20 1 1 1 2020-01-19
This is why you can’t use order by ind1 desc, ind2 desc, date desc (which is quite tempting).