请问一个分组排序的问题，非常感谢大虾
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 ', 'yyyy-mm-dd ') as mydate
32 from dual
33 union all
34 select 6 as ID,
35 2 as site,
36 '单位2 ' as name,
37 to_date( '2002-01-01 ', 'yyyy-mm-dd ') as mydate
38 from dual) tt) zz
39 where zz.rn <= 2;
===================result==============================
ID SITE NAME MYDATE RN
---------- ---------- --------- ----------- ----------
2 1 单位1 1/1/2005 1
3 1 单位1 1/1/2004 2
5 2 单位2 1/1/2005 1
4 2 单位2 1/1/2003 2
