求一SQL语句,有关group by的问题

[lolo2006]

主键
id fid
1 18
2 17
3 19
4 17
5 18
6 19
7 17

想得到:
3 19
5 18
7 17
也就是分组查询出所有fid相同的记录,并按id升序排列各个分组得到最后一条(每个分组只要一条记录)

[lolo2006]

顶一个

[hackzhuang]

路过踩一脚

[邪V风]

select count(fid) as aa,fid from table where fid in (select fid from table) order by fid desc

[lolo2006]

回楼上:
应该用group by吧
我可能没说清楚
我是要查询出fid中每个相同分组中一条记录,这条记录是id升序排列后的最后一条

[chjpeng]

create table #t(id int,fid int)
insert into #t select 1,18
union all select 2,17
union all select 3,19
union all select 4,17
union all select 5,18
union all select 6,19
union all select 7,17

select max(id) as id,fid from #t group by fid order by max(id)

[tippointGmail]

如果你的数据中有没有重复的数据,按

select max(id),fid from #t group by fid having count(fid)>1 order by max(id)

[lolo2006]

我是access,测试不能通过,星星指点下

[lolo2006]

ok,搞定!
chjpeng(鹏&静水之潭,苦乐随缘)正解!
结贴!