现有一个表table1,字段有id,group,vlaue.有9条数据,依下
id group value
1 A 9
1 A 5
1 A 3
1 B 1
1 B 8
1 B 2
1 C 10
1 C 7
1 C 4我现在想得到每个分组的最大值,依次排出,也就是以下这种结果,不知道sql语句如何写?请大侠们帮帮忙。
1 A 9
2 B 8
3 C 10 4 A 5
5 B 2
6 C 7 7 A 3
8 B 1
9 C 4
id group value
1 A 9
1 A 5
1 A 3
1 B 1
1 B 8
1 B 2
1 C 10
1 C 7
1 C 4我现在想得到每个分组的最大值,依次排出,也就是以下这种结果,不知道sql语句如何写?请大侠们帮帮忙。
1 A 9
2 B 8
3 C 10 4 A 5
5 B 2
6 C 7 7 A 3
8 B 1
9 C 4
group1 varchar2(10), -- group 是关键字,不能用作字段名
value number(18,0)
);insert into table1(id,group1,value) values(1,'A',9);
insert into table1(id,group1,value) values(1,'A',5);
insert into table1(id,group1,value) values(1,'A',3);
insert into table1(id,group1,value) values(1,'B',1);
insert into table1(id,group1,value) values(1,'B',8);
insert into table1(id,group1,value) values(1,'B',2);
insert into table1(id,group1,value) values(1,'C',10);
insert into table1(id,group1,value) values(1,'C',7);
insert into table1(id,group1,value) values(1,'C',4);commit;with a as(select row_number() over(partition by group1 order by value desc) as rn, group1, value from table1),
b as(select a.group1, a.value from a order by a.rn asc, a.group1 asc)
select rownum, b.group1, b.value from b;
2 b as(select a.group1, a.value from a order by a.rn asc, a.group1 asc)
3 select rownum, b.group1, b.value from b; ROWNUM GROUP1 VALUE
---------- -------------------- ----------
1 A 9
2 B 8
3 C 10
4 A 5
5 B 2
6 C 7
7 A 3
8 B 1
9 C 4已选择9行。
with t as
(
select 1 as id, 'A' as grp, 9 as val from dual
union all
select 1 as id, 'A' as grp, 5 as val from dual
union all
select 1 as id, 'A' as grp, 3 as val from dual
union all
select 1 as id, 'B' as grp, 1 as val from dual
union all
select 1 as id, 'B' as grp, 8 as val from dual
union all
select 1 as id, 'B' as grp, 2 as val from dual
union all
select 1 as id, 'C' as grp, 10 as val from dual
union all
select 1 as id, 'C' as grp, 7 as val from dual
union all
select 1 as id, 'C' as grp, 4 as val from dual
union all
select 1 as id, 'C' as grp, 6 as val from dual
)
select grp, val
from (select grp, val, row_number() over(partition by grp order by val desc) as lv
from t a
order by lv, grp)
drop table if exists test;
create table test
(
id int ,
tgroup varchar(10),
tvalue int
);insert into test values
(1,'A',10),
(1,'A',9),
(1,'A',8),
(1,'B',7),
(1,'B',6),
(1,'B',5),
(1,'C',4),
(1,'C',3),
(1,'C',2);
set @i=0 ;
select @i:=@i+1, c.tgroup , c.tvalue from (select a.* , count(b.id) seq from test a , test b where a.tgroup=b.tgroup and a.tvalue>=b.tvalue group by a.tvalue) c order by c.seq desc , c.tvalue desc