sql 分组后 查询 每个组的第一条

[songguanlgun]

bh rq ZLID ID

3456 2013/5/6 0:00:00 6565656 1
3456 2013/5/6 0:00:00 6565656 2
1234 2013/3/22 17:23:47 0661b5c372a54be3b3229afaf1cc9a9a 3
1234 2013/5/16 0:00:00 27ffd5ccae1a4c8893fcde432643efbc 4
1234 2013/5/20 0:00:00 d5759ffb7d6e41a4bea65c3f1001ff3b 5
3456 2013/5/6 0:00:00 6565656 7

按bh分组按日期降序 查询每个分组的第一条
结果如
3456 2013/5/6 0:00:00 6565656 1
1234 2013/5/20 0:00:00 d5759ffb7d6e41a4bea65c3f1001ff3b 5

[LongRui888]

select bh,rq,ZLID,ID                            
from 
(
select *,
       ROW_NUMBER() over(PARTITION by bh order by rq desc ) rownum
from 表
)t
where rownum= 1

[Yole]

select * from table a,
(
select bh ,max(rq) as rq  ,min(ID) as id   from table group by  bh 
) b 
where a.id=b.id and a.bh=b.bh,a.rq=b.rq

[LongRui888]

create table tb(bh int,rq datetime,ZLID varchar(50),ID int)

                            
insert into tb 
select 3456 	,'2013/5/6 0:00:00'	,'6565656'           ,	1 union all
select 3456   	,'2013/5/6 0:00:00'	,'6565656'        ,2 union all
select 1234   	,'2013/3/22 17:23:47','0661b5c372a54be3b3229afaf1cc9a9a',3 union all
select 1234    	,'2013/5/16 0:00:00','27ffd5ccae1a4c8893fcde432643efbc',4 union all
select 1234   	,'2013/5/20 0:00:00','d5759ffb7d6e41a4bea65c3f1001ff3b',5 union all
select 3456  	,'2013/5/6 0:00:00','6565656',        	7
go


select bh,rq,ZLID,ID                            
from 
(
select *,
       ROW_NUMBER() over(PARTITION by bh order by rq desc,id ) rownum
from tb
)t
where rownum= 1
order by ID
/*
bh	rq	ZLID	ID
3456	2013-05-06 00:00:00.000	6565656	1
1234	2013-05-20 00:00:00.000	d5759ffb7d6e41a4bea65c3f1001ff3b	5
*/

[sqlkxr]

select * from table a where rq=(select max(rq) from table where bh=a.bh )

[铁歌]

select * From tablename a inner join ( select bh ,max(rq) rq from tablename group by bh) b on a.bh =b.bh and a.rq =b.bh

[铁歌]

select * From tablename a inner join ( select bh ,max(rq) rq from tablename group by bh) b on a.bh =b.bh and a.rq =b.bh