怎样得到日期第二新的记录?
IF   EXISTS(SELECT   name  
    FROM     sysobjects  
    WHERE     name   =   N 'T '  
    AND     type   =   'U ')
        DROP   TABLE   T
GO

CREATE   TABLE   T   (
C1   int,
C2   char(10),
C3   datetime)

GO

insert   into   T  
select   1, 'AA ', '2006-10-11 '   union   all
select   2, 'AA ', '2006-11-11 '   union   all
select   3, 'AA ', '2006-12-11 '   union   all
select   4, 'AA ', '2007-01-01 '   union   all
select   5, 'BB ', '2006-10-11 '   union   all
select   6, 'BB ', '2006-11-11 '   union   all
select   7, 'BB ', '2006-12-11 '   union   all
select   8, 'BB ', '2007-01-01 '   union   all
select   9, 'CC ', '2006-10-11 '  

以上的表中,我想得到按C2分组,日期第二新的记录怎么写sql?
如:
3,AA,2006-12-11
7,BB,2006-12-11  
9,CC,2006-10-11


------解决方案--------------------
select T.* from T
inner join
(select min(c3) as c3, c2
from (select c1,c2,c3 from T where 2> (select count(*) from T a where a.c2=T.c2 and a.c3> T.c3)) T1
group by c2) T2
on T.c2=T2.c2 and T.c3=T2.c3

/*
C1 C2 C3
----------- ---------- ------------------------------------------------------
3 AA 2006-12-11 00:00:00.000
7 BB 2006-12-11 00:00:00.000
9 CC 2006-10-11 00:00:00.000
*/
------解决方案--------------------
select * from T as tmp
where (select count(*) from T where C2=tmp.C2 and C3 <tmp.C3)=2 or
(select count(*) from T where C2=tmp.C2)=1

--result
C1 C2 C3
----------- ---------- ------------------------------------------------------
3 AA 2006-12-11 00:00:00.000
7 BB 2006-12-11 00:00:00.000
9 CC 2006-10-11 00:00:00.000

(3 row(s) affected)