已知表tab1,有字段和记录
col1 col2 col3
r1 1 01
r1 2 02r2 3 03
r2 4 02r3 5 01
r3 6 04怎么写sql语句,按col1分组,求组内最大的col2值的col3,查询结果为:
r1 2 02
r2 4 02
r3 6 04
col1 col2 col3
r1 1 01
r1 2 02r2 3 03
r2 4 02r3 5 01
r3 6 04怎么写sql语句,按col1分组,求组内最大的col2值的col3,查询结果为:
r1 2 02
r2 4 02
r3 6 04
with a as
(
select 'r1' col1,1 col2,'01' col3 from dual
union all
select 'r1' col1,2 col2,'02' col3 from dual
union all
select 'r2' col1,3 col2,'03' col3 from dual
union all
select 'r2' col1,4 col2,'02' col3 from dual
union all
select 'r3' col1,5 col2,'01' col3 from dual
union all
select 'r3' col1,6 col2,'04' col3 from dual
)
select a2.*
from (
select a1.col1,max(a1.col2) max_col2
from a a1
group by col1 ) t,a a2
where t.col1 = a2.col1
and t.max_col2 = a2.col2
from (
select col1,col2,col3,
row_number() over(partition by col1order by col2desc) rn
from tab1
)t1
where rn = 1
group by t.a )
select a.* from a,test
where a.a=test.a
and a.b=test.bmax
select 1 from tab1 t2 where t1.col1=t2.col2 and t2.col2>t1.col2);
(
SELECT tab1.*, (row_number() OVER (PARTITION BY col1 ORDER BY col2 DESC)) AS num FROM tab1
) temp
where num=1
;
select a.a,a.b,a.c from tab1 a,(select a,max(b) b from tab1 group by a) b
where a.a=b.a and a.b=b.b
(select col1,max(col2) from tab1 group by col1)
(
Col1 VARCHAR2(20),
Col2 NUMBER(4),
Col3 VARCHAR2(20)
);INSERT INTO T120 VALUES('r1', 1, '01');
INSERT INTO T120 VALUES('r1', 2, '02');
INSERT INTO T120 VALUES('r2', 3, '03');
INSERT INTO T120 VALUES('r2', 4, '04');
INSERT INTO T120 VALUES('r3', 5, '05');
INSERT INTO T120 VALUES('r3', 6, '06');
实测结果:
insert into tblTest values('r1',1,'01');
insert into tblTest values('r1',2,'02');
insert into tblTest values('r2',3,'03');
insert into tblTest values('r2',4,'04');
insert into tblTest values('r3',5,'05');
insert into tblTest values('r3',6,'06');
commit;
select col1,col2,col3 from tblTest where
(col1,col2) in(
select col1,max(col2) from tblTest
group by col1)
(
select 'r1' col1,1 col2,'01' col3 from dual
union all
select 'r1' col1,2 col2,'02' col3 from dual
union all
select 'r2' col1,3 col2,'03' col3 from dual
union all
select 'r2' col1,4 col2,'02' col3 from dual
union all
select 'r3' col1,5 col2,'01' col3 from dual
union all
select 'r3' col1,6 col2,'04' col3 from dual
)
Select col1, col2, col3
From (Select t.*, row_number() over(Partition By col1 Order By col2 Desc) rn
From tb_data t)
Where rn = 1;