请问一个查询

请教一个查询
请教我想查询时返回的记录中取item对应的edate最大值。
item不能重复。

item                           price                             sdate                     edate

59302.60538000002011-06-30   00:00:00.0002011-07-12   16:40:55.873
59302.89899000002011-09-17   00:00:00.0002011-09-23   10:54:09.357
59302.42016000002012-02-26   00:00:00.0002012-02-20   12:00:40.543
59360.19620000002010-07-01   00:00:00.0002010-07-14   00:00:00.000
59360.20094000002010-08-25   00:00:00.0002010-09-21   13:45:53.140
59360.20716000002010-09-26   00:00:00.0002010-10-26   13:53:33.390
59360.20716300002010-09-26   00:00:00.0002010-11-04   14:47:27.357
59360.22778600002010-10-21   00:00:00.0002010-12-10   18:15:51.373
122001.49496000002010-07-01   00:00:00.0002010-07-14   00:00:00.000
122001.49834000002010-08-25   00:00:00.0002010-09-21   13:45:53.140
122001.56361000002010-09-26   00:00:00.0002010-10-26   13:53:33.390
122001.56360500002010-09-26   00:00:00.0002010-11-04   14:47:27.357

[解决办法]

SQL code

--2005以上版本select item,price,sdate,edate from(select *,row_number()over(partition by item order by edate desc)as id from tbl) a where id=1--2000版本select * from tbl a where edate=(select max(edate) from tbl b where a.item=b.item)
[解决办法]
SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (item int,price numeric(11,10),sdate datetime,edate datetime)insert into [TB]select 5930,2.6053800000,'2011-06-30 00:00:00.000','2011-07-12 16:40:55.873' union allselect 5930,2.8989900000,'2011-09-17 00:00:00.000','2011-09-23 10:54:09.357' union allselect 5930,2.4201600000,'2012-02-26 00:00:00.000','2012-02-20 12:00:40.543' union allselect 5936,0.1962000000,'2010-07-01 00:00:00.000','2010-07-14 00:00:00.000' union allselect 5936,0.2009400000,'2010-08-25 00:00:00.000','2010-09-21 13:45:53.140' union allselect 5936,0.2071600000,'2010-09-26 00:00:00.000','2010-10-26 13:53:33.390' union allselect 5936,0.2071630000,'2010-09-26 00:00:00.000','2010-11-04 14:47:27.357' union allselect 5936,0.2277860000,'2010-10-21 00:00:00.000','2010-12-10 18:15:51.373' union allselect 12200,1.4949600000,'2010-07-01 00:00:00.000','2010-07-14 00:00:00.000' union allselect 12200,1.4983400000,'2010-08-25 00:00:00.000','2010-09-21 13:45:53.140' union allselect 12200,1.5636100000,'2010-09-26 00:00:00.000','2010-10-26 13:53:33.390' union allselect 12200,1.5636050000,'2010-09-26 00:00:00.000','2010-11-04 14:47:27.357'select * from [TB]select distinct B.item,b.price,b.sdate,b.edatefrom TB Across apply(select top 1 item,price,sdate,edate from TB where item = A.item order by edate desc) B/*5930    2.4201600000    2012-02-26 00:00:00.000    2012-02-20 12:00:40.5435936    0.2277860000    2010-10-21 00:00:00.000    2010-12-10 18:15:51.37312200    1.5636050000    2010-09-26 00:00:00.000    2010-11-04 14:47:27.357*/
[解决办法]
SQL code
select a.* from tab ainner join(select item,max(edate) maxedatefrom tab group by item) bon a.item=b.item and a.edate=b.maxedate
[解决办法]
SQL code
selct distinct item,max(edate) from 表 gourp by item
------解决方案-------------------- 
 
SQL code
或者：select * from tbl a where not exists(     select 1 from tbl b where a.item=b.item and a.edate<b.edate)