问一SQL
Table   A

UserNo       ProductNo     DirectorNo       EndDate
---------------------------------------------
1                           1                 3                         2007-01-08
1                           2                 2                         2007-05-08
2                           1                 1                         2007-08-09


SELECT   UserNo,   ProductNo,   MAX(EndDate)   FROM   [A]
GROUP   UserNo,   ProductNo

如果结果要多加一   DirectorNo   列,   DirectorNo的值等于   MAX(EndDate)   的   DirectorNo

怎么写？？
谢谢

------解决方案--------------------
select * from [A]
inner join
(SELECT UserNo, ProductNo, MAX(EndDate) EndDate FROM [A]
GROUP By UserNo, ProductNo) b

on a.UserNo=b.UserNo and a.ProductNo=b.ProductNo and a.EndDate =b.EndDate
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(UserNo int,ProductNo int,DirectorNo int,EndDate datetime)
insert into tb(UserNo,ProductNo,DirectorNo,EndDate) values(1,1,3, '2007-01-08 ')
insert into tb(UserNo,ProductNo,DirectorNo,EndDate) values(1,2,2, '2007-05-08 ')
insert into tb(UserNo,ProductNo,DirectorNo,EndDate) values(2,1,1, '2007-08-09 ')

select a.* from tb a,
(SELECT UserNo, ProductNo, MAX(EndDate) as enddate FROM tb GROUP by UserNo, ProductNo) b
where a.UserNo = b.UserNo and a.ProductNo=b.ProductNo and a.EndDate = b.EndDate

drop table tb
/*
UserNo ProductNo DirectorNo EndDate
----------- ----------- ----------- -----------------------
2 1 1 2007-08-09 00:00:00.000
1 2 2 2007-05-08 00:00:00.000
1 1 3 2007-01-08 00:00:00.000
*/