[高手进]-----查询各天nn值最大的pid

[姓庄滴]

有表如下:

表名:test

ftime pid nn
------------------------------
2006-12-05 | aa | 12
2006-12-05 | bb | 11
2006-12-05 | cc | 12
2006-12-06 | aa | 9
2006-12-06 | bb | 25
2006-12-07 | bb | 32
2006-12-07 | cc | 26
------------------------------


问题:
返回各天nn最大的那条记录 (若nn并列最大,一起返回)

也就是说，我想要从上面的test表中，查询出下面这样的返回结果。

ftime pid nn
------------------------------
2006-12-05 | aa | 12 |
2006-12-05 | cc | 12 |------>5日这天,这两条并列最大,所以要返回
2006-12-06 | bb | 25 ------>6日这天,这条的“nn”最大,所以返回
2006-12-07 | bb | 32 ------>7日这天,这条的“nn”最大,所以返回
------------------------------


请问：其SQL语句如何写？

[caixia615]

select * from tablename where nn in(select max(nn) from tablename group by ftime)

[冷箫轻笛]

select * from test t1
where not exists(select 1 from test where ftime = t1.ftime and nn > t1.nn)

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
ftime datetime,
pid varchar(10),
nn int
)

insert into tb(ftime,pid,nn) values('2006-12-05','aa',12)
insert into tb(ftime,pid,nn) values('2006-12-05','bb',11)
insert into tb(ftime,pid,nn) values('2006-12-05','cc',12)
insert into tb(ftime,pid,nn) values('2006-12-06','aa',9)
insert into tb(ftime,pid,nn) values('2006-12-06','bb',25)
insert into tb(ftime,pid,nn) values('2006-12-07','bb',32)
insert into tb(ftime,pid,nn) values('2006-12-07','cc',26)

select convert(varchar(10),a.ftime,120) as ftime , a.pid , a.nn from tb a,
(select ftime , max(nn) as nn from tb group by ftime) b
where a.ftime = b.ftime and a.nn = b.nn
order by a.ftime , a.pid

drop table tb

ftime pid nn
---------- ---------- -----------
2006-12-05 aa 12
2006-12-05 cc 12
2006-12-06 bb 25
2006-12-07 bb 32

（所影响的行数为 4 行）