# 根据案例陈述从分组中选择

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

• 笑狠徦 回复

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

如果您喜欢算术，可以将其简化为：

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

请注意，将逻辑组合在一起的一个技巧是确保在以下情况下使用第一行：

``````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).