这个SQL怎么写啊?
有一个表   a   ,有如下数据

id     type     time
1           a       20070605
2           b       20070606
3           b       20070530
4           a       20070610
5           c       20070609
6           a       20070608
7           b       20070601
8           a       20070602
9           c       20070612
10         c       20070623

请问如何获得的每个   type   按时间desc排序的前2项啊??是每个   type   都返回2项.

就是返回结果如下:

id       type     time
4           a       20070610
6           a       20070608
2           b       20070606
7           b       20070601
10         c       20070623
9           c       20070612

在线等.

------解决方案--------------------
select *
from (select t.*,
count(*) over(partition by type order by time desc) as cnt
from a t) tt
where tt.cnt <= 2
------解决方案--------------------
create table test2(id int, type varchar2(10), time varchar2(100));

insert into test2
select 1, 'a ', '20070605 ' from dual
union all
select 2, 'b ', '20070606 ' from dual
union all
select 3, 'b ', '20070530 ' from dual
union all
select 4, 'a ', '20070610 ' from dual
union all
select 5, 'c ', '20070609 ' from dual
union all
select 6, 'a ', '20070608 ' from dual
union all
select 7, 'b ', '20070601 ' from dual
union all
select 8, 'a ', '20070602 ' from dual
union all
select 9, 'c ', '20070612 ' from dual
union all
select 10, 'c ', '20070623 ' from dual
--执行sql
select id,type,time from
(select id,type,time,row_number()over(partition by type order by time desc) inx from test2) t
where t.inx <=2
--Result
1 4 a 20070610
2 6 a 20070608
3 2 b 20070606
4 7 b 20070601
5 10 c 20070623
6 9 c 20070612