sql取出时间 最大最小和第二大如何取？
create table #a(code varchar(50),dt1 datetime)
insert into #a(code,dt1) values(1001,'2013-07-02 08:51:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:52:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:53:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:55:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:57:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:58:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:31:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:33:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:45:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:47:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:56:18')
select * from #a
可以看到 1001 有6条数据，1002 有5条数据
我现在想 根据code分类，把 时间最小，时间最大，时间第二大 的三条数据查询出来
最后得到的结果为
code dt1
10012013-07-02 08:51:18.000
10012013-07-02 08:57:18.000
10012013-07-02 08:58:18.000
10022013-07-02 08:31:18.000
10022013-07-02 08:47:18.000
10022013-07-02 08:56:18.000
[解决办法]
create table #a(code varchar(50),dt1 datetime)
insert into #a(code,dt1) values(1001,'2013-07-02 08:51:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:52:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:53:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:55:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:57:18')
insert into #a(code,dt1) values(1001,'2013-07-02 08:58:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:31:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:33:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:45:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:47:18')
insert into #a(code,dt1) values(1002,'2013-07-02 08:56:18')
SELECT * FROM
(select row_number() over (partition by code order by dt1 desc) as id ,* from #a) t
WHERE id=1 OR id=2 OR id=(SELECT count(1) FROM #a b WHERE b.code=t.code)
/*
id code dt1
-------------------- -------------------------------------------------- -----------------------
1 1001 2013-07-02 08:58:18.000
2 1001 2013-07-02 08:57:18.000
6 1001 2013-07-02 08:51:18.000
1 1002 2013-07-02 08:56:18.000
2 1002 2013-07-02 08:47:18.000
5 1002 2013-07-02 08:31:18.000
*/
drop table #a
