请问一个分组排序的问题，非常感谢大虾
table1   数据如下
id       site       name           mydate
1         1             单位1       2001-1-1
2         1             单位1       2005-1-1
3         1             单位1       2004-1-1
4         2             单位2       2003-1-1
5         2             单位2       2005-1-1
6         2             单位2       2002-1-1


按site分组，取出每个site最新2条记录(按mydate降序)，查询结果如下：

id       site       name           mydate
2         1             单位1       2005-1-1
3         1             单位1       2004-1-1

5         2             单位2       2005-1-1
4         2             单位2       2003-1-1

请问SQL   怎么写，谢谢了

------解决方案--------------------
select id,site,name,mydate
from (select id,site,name,mydate,
row_number() over (partition by site order by mydate desc nulls last) rn)
where rn <=2
------解决方案--------------------
select id,site,name,mydate
from (select id,site,name,mydate,
row_number() over (partition by site order by mydate desc nulls last) rn
from table)
where rn <=2
------解决方案--------------------
SQL> select *
2 from (select tt.*,
3 row_number() over(partition by tt.site order by tt.mydate desc) rn
4 from (select 1 as ID,
5 1 as site,
6 '单位1 ' as name,
7 to_date( '2001-01-01 ', 'yyyy-mm-dd ') as mydate
8 from dual
9 union all
10 select 2 as ID,
11 1 as site,
12 '单位1 ' as name,
13 to_date( '2005-01-01 ', 'yyyy-mm-dd ') as mydate
14 from dual
15 union all
16 select 3 as ID,
17 1 as site,
18 '单位1 ' as name,
19 to_date( '2004-01-01 ', 'yyyy-mm-dd ') as mydate
20 from dual
21 union all
22 select 4 as ID,
23 2 as site,
24 '单位2 ' as name,
25 to_date( '2003-01-01 ', 'yyyy-mm-dd ') as mydate
26 from dual
27 union all
28 select 5 as ID,
29 2 as site,
30 '单位2 ' as name,
31 to_date( '2005-01-01 ',