关于取出最近一次日期的sql语句,该如何解决

关于取出最近一次日期的sql语句
比如表#t数据如下：

SQL code

id   productname      listdate1        A              2011-01-022        B              2011-01-053        A              2010-02-034        C              2011-03-045        A              2011-04-056        B              2011-01-01

SQL code

id   productname      listdate         listdate11        A              2011-01-02       2010-02-032        B              2011-01-05       2011-01-013        A              2010-02-03       null4        C              2011-03-04       null5        A              2011-04-05       2011-01-026        B              2011-01-01       null

SQL code

SELECT * ,    (select min(listdate) from #t B where a.productname = b.productname             and b.listdate > a.listdate) as listdate1FROM #t A
[解决办法]
SQL code
select n.id.miproductname,n.listdate,m.listdate1 from (select id,productname,listdate listdate1 from test awhere not exists(select 1 from test b where a.productname=b.productname and a.listdate <b.listdate ))tright join test non ty.id=n.id
[解决办法]

？LZ？最近一次的日期是按照什么排序的呢？
SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (id int,productname nvarchar(2),listdate datetime)insert into [TB]select 1,'A','2011-01-02' union allselect 2,'B','2011-01-05' union allselect 3,'A','2010-02-03' union allselect 4,'C','2011-03-04' union allselect 5,'A','2011-04-05' union allselect 6,'B','2011-01-01'select * from [TB]SELECT  id ,        productname ,        listdate ,        ( SELECT TOP 1                    listdate          FROM      TB          WHERE     productname = A.Productname                    AND listdate <> A.listdate                    AND id >A.id          ORDER BY  listdate asc        ) AS listdate2FROM    dbo.TB A/*id          productname listdate                listdate2----------- ----------- ----------------------- -----------------------1           A           2011-01-02 00:00:00.000 2010-02-03 00:00:00.0002           B           2011-01-05 00:00:00.000 2011-01-01 00:00:00.0003           A           2010-02-03 00:00:00.000 2011-04-05 00:00:00.0004           C           2011-03-04 00:00:00.000 NULL5           A           2011-04-05 00:00:00.000 NULL6           B           2011-01-01 00:00:00.000 NULL(6 row(s) affected)*/
[解决办法]
探讨

SQL code

SELECT * ,
    (select min(listdate) from #t B where a.productname = b.productname 
            and b.listdate > a.listdate) as listdate1

FROM #t A