这个表是以name1,zs desc排序的,如何取出每个分组的第一条记录(黄色背景)
------最佳解决方案--------------------
select *
from table1 t1
where not exists (select 1
from table1 t2
where t2.name1 = t1.name1
and t2.zs > t1.zs) order by t1.name1;
NAME1 NAME2 ZS
----- ----- --
aa a2 5
bb b1 20
cc c2 30
cc c1 30
dd d 40
cc有两条都是33的，应该是取两条吧？要不然你随便取其中一条？
------其他解决方案--------------------
收工接分。
WITH table1 AS
(
SELECT 'aa' AS name1, 'a1' AS name2,'30' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a2' AS name2,'5' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a3' AS name2,'3' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a4' AS name2,'3' AS zs FROM dual
union all
SELECT 'bb' AS name1, 'b1' AS name2,'20' AS zs FROM dual
union all
SELECT 'bb' AS name1, 'b2' AS name2,'11' AS zs FROM dual
union all
SELECT 'cc' AS name1, 'c1' AS name2,'30' AS zs FROM dual
union all
SELECT 'cc' AS name1, 'c2' AS name2,'30' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd' AS name2,'40' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd1' AS name2,'25' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd2' AS name2,'20' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd3' AS name2,'0' AS zs FROM dual
)
select name1,name2,zs from (
select row_number()over(partition by name1 order by name2,zs desc) as rn ,t.* from table1 t) where rn = 1
------其他解决方案--------------------
select *
from t_talbe
where (name1,zs) in (
select t.name1,max(t.zs)