oracle数据分组问题

Question

现有表tab数据如下，想对其进行分组排名统计；col1  col2  col3  col4
1      a     4     6
2      a     5     3
3      a     63    34
4      b     35    34
5      b     344   43
6      b     34     45
7      c     34    43
8      c     45     34
9      c     31     41对col2分组统计，只要求前两名的数据（先比较col3的数据，再比较col4的数据），结果如下
col1  col2  col2  col4
3     a      63    34
2     a       5     3
5     b       344   43
4     b        35  34
8     c        45   34
7     c        34   43

Answer 1

easyselect col1,col2,col3,col4
from(
select tab.*,row_number() over(partition by col2 order by col3 desc,col4 desc) rn
from tab)
where rn<=2
order by col2,rn

Answer 2

select col1, col2, col3, col4
  from (select col1,
               col2,
               col3,
               col4,
               row_number() over(partition by col2 order by col3 desc) rn
          from a)
 where rn < 3

Answer 3

SQL> select * from t;                                   COL1 COL2                                                COL3                                    COL4
--------------------------------------- ---------------- --------------------------------------- ---------------------------------------
                                      1 a                                                      4                                       6
                                      2 a                                                      5                                       3
                                      3 a                                                     63                                      34
                                      4 b                                                     35                                      34
                                      5 b                                                    344                                      43
                                      6 b                                                     34                                      45
                                      7 c                                                     34                                      43
                                      8 c                                                     45                                      34
                                      9 c                                                     31                                      419 rows selectedSQL> 
SQL> select col1,col2,col3,col4 from (
  2      select col1,col2,col3,col4,
  3      row_number()over(partition by col2 order by col3 desc) rm from t
  4  ) where rm <=2;                                   COL1 COL2                                                COL3                                    COL4
--------------------------------------- ---------------- --------------------------------------- ---------------------------------------
                                      3 a                                                     63                                      34
                                      2 a                                                      5                                       3
                                      5 b                                                    344                                      43
                                      4 b                                                     35                                      34
                                      8 c                                                     45                                      34
                                      7 c                                                     34                                      436 rows selected

Answer 4

刚刚找了下，还可以用
RANK() OVER (PARTITION BY COL2 ORDER BY COL3 DESC) 来的，
谢谢大家了