分组查询 获取每一组的第一条记录 SQL怎么写?

[mrmatm]

value type
1 A
3 A
4 B
8 A
9 B
5 B
怎么按type分组查询 获取每一组的第一条记录 SQL怎么写?

[winstonbonaparte]

select value = min(value) from 表A group by type

[-狙击手-]

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

[-狙击手-]

select type,value = min(value) 
from 表A 
group by type

select type,value = max(value) 
from 表A 
group by type

[huangjh_love]

select * from tb a
where exists(select 1 from tb where [type]=a.type and [values]<=a.[values])
group by a.type

[zccmy22]

select type,max(value) as value from 表 group by type

[dawugui]

[Quote=引用楼主 mrmatm 的帖子:]
value type
1 A
3 A
4 B
8 A
9 B
5 B
怎么按type分组查询 获取每一组的第一条记录 SQL怎么写?
[/Quote]
貌似可以用:

select min(value) value, type from tb group by type

[dawugui]

create table tb(value int,type varchar(10))
insert into tb values(1 ,    'A') 
insert into tb values(3 ,    'A') 
insert into tb values(4 ,    'B') 
insert into tb values(8 ,    'A') 
insert into tb values(9 ,    'B') 
insert into tb values(5 ,    'B') 
go

--根据你提供数据的特殊性，可用下法。
select min(value) value, type from tb group by type
/*
value       type       
----------- ---------- 
1           A
4           B
（所影响的行数为 2 行）
*/

--如果不是最小的value排在最前面，则如下：
select a.* from tb a where value = (select top 1 value from tb where type = a.type) order by a.type
/*
value       type       
----------- ---------- 
1           A
4           B
（所影响的行数为 2 行）
*/

drop table tb

[mrmatm]

value值不是数字!是字符串