如何查询group by中的某列的最大值的返回列

[lg314]

说的不是很清楚

create table #ls(barcode varchar(30),vendorid char(6),indate datetime,price float)
insert into #ls(barcode,vendorid,indate,price)
select '11','01','2009-01-01',0.1 union all
select '11','02','2009-02-01',0.7 union all
select '11','03','2009-02-03',0.9 union all
select '11','01','2009-05-01',0.2 union all
select '11','02','2009-06-01',1.4 union all
select '11','03','2009-07-03',1.8 union all
select '21','01','2009-01-01',0.1 union all
select '21','02','2009-02-01',0.7 union all
select '21','03','2009-02-03',0.9 union all
select '21','01','2009-05-01',0.3 union all
select '21','02','2009-06-01',2.1 union all
select '21','03','2009-07-03',2.7



select b.barcode,b.vendorid,b.price from (select barcode,vendorid,max(indate) as indate from #ls group by barcode,vendorid) a 
left join #ls b on a.barcode=b.barcode and a.vendorid=b.vendorid and a.indate=b.indate

就是说找出barcode和vendorid对应的indate最晚日期的price

[--小F--]

http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html?85256

[zxkid]

运用RANK()函数

[lg314]

直接返回的方法,不用嵌套

[水族杰纶]

--try
select * from #ls t
where not exists(select 1 from #ls where barcode=t.barcode and vendorid=t.vendorid 
and indate>t.indate)

[百年树人]

select t.barcode,t.vendorid,t.price 
from #ls t
where not exists(select 1 from #ls where 
barcode=t.barcode and vendorid=t.vendorid and indate>t.indate)

[lg314]

呵呵,非常不错

[SQL77]

select * from #ls t
where INDAETE=(select MAX(INDATER) from #ls where barcode=t.barcode and vendorid=t.vendorid )

[白头老汉]

做一个内连接，用indate=max（indate）
select barcode,vendorid,price from #ls as a where a.indate=(select max(indate) from #ls where #ls.barcode=a.barcode and #ls.vendorid=a.vendorid)