分类查询最大值记录，每类只要一条(100分)

[Luxm]

分类查询最大值记录，每类如果有多个最大值按ID取最前一条
表A
id tpye value
1 1 34
2 2 23
3 3 39
4 1 34
5 2 25
6 3 23
7 1 33
8 2 25
9 3 38
10 1 26
查询结果
1 1 34
5 2 25
3 3 39

[smart_zcg]

select * from a as b
where not exists (select * from a where b.id=id and value>b.value)

[akuzou]

select * from A t where id in (select top 1 id from A where type=t.type order by value desc,id)

[子陌红尘]

select t.* from 表A where not exists(select 1 from 表A where type=t.type and id<t.id)

select t.* from 表A where t.id=(select top 1 id from 表A where type=t.type order by id)

select t.* from 表A where t.id=(select min(id) from 表A where type=t.type)

[子陌红尘]

select t.* from 表A t where not exists(select 1 from 表A where type=t.type and id<t.id) order by t.type

select t.* from 表A t where t.id=(select top 1 id from 表A where type=t.type order by id) order by t.type

select t.* from 表A t where t.id=(select min(id) from 表A where type=t.type) order by t.type

[smart_zcg]

前面错了，呵呵
select * from a as b
where not exists (select * from a where b.type=type and value>b.value)

[zhaoanle]

1.
select * from a as b where not exists(select 1 from a where type=b.type and value>b.value)

2.
select * from a as b where id=(select top 1 id from a where type=b.type order by value desc)

[leo_lesley]

create table tab(id int,tpye int, value int)
insert tab

select 1,1,34
union
select 2,2,23
union
select 3,3,39
union
select 4,1,34
union
select 5,2,25
union
select 6,3,23
union
select 7,1,33
union
select 8,2,25
union
select 9,3,38
union
select 10,1,26

select t.*
from tab t
where not exists(select 1 from tab where tpye=t.tpye and id<t.id)

[jacobsan]

create table #(id int, tpye int, value int)
insert into # select 1 , 1 , 34
union all select 2 , 2 , 23
union all select 3 , 3 , 39
union all select 4 , 1 , 34
union all select 5 , 2 , 25
union all select 6 , 3 , 23
union all select 7 , 1 , 33
union all select 8 , 2 , 25
union all select 9 , 3 , 38
union all select 10 , 1 , 26

select id=min(id),tpye,value=max(value) from (select * from # as b where not exists (select * from # where b.tpye=tpye and value>b.value))t group by tpye


---
1 1 34
5 2 25
3 3 39

[jacobsan]

或者
select t.* from # t where t.id=(select top 1 id from # where tpye=t.tpye order by value desc,id) order by t.tpye