select * from (
select zz.*,row_number() over(paritiion by zz.warnIndCode order by zz.warnLightTtpe asc) yn
-- 上面为对指标进行分组,然后组内对灯类型进行升序排列,取组内第一个
from (
select * from (
select h.*,row_number() over(partition by h.groupId order by h.ruleCode desc ) rn
from(
-- 上面对groupId 进行分组,然后对rulecode降序排列,取ruleCode最大的一个
select
a.warn_ind_code warnIndCode,
a.warn_light_type warnLightType,
a.group_id groupId,
a.rule_code ruleCode
from
test a ) h ) t where t.rn <=1
) zz) ff where ff.yn<=1
--------------------------------------------------