数据的双重排序

 

SELECT G.*, ROW_NUMBER() OVER(PARTITION BY a ,b ORDER BY c DESC) ROWN
2    from (select '1' a, '2' b, '1' c
3            from dual
4          union all
5          select '1', '2', '2'
6            from dual
7          union all
8          select '1', '3', '3'
9            from dual
10          union all
11          select '1', '3', '4'
12            from dual
13          union all
14          select '1', '4', '5' from dual
15          union all
16          select '1','3','5'from dual) G
17 ;

A B C       ROWN
- - - ----------
1 2 2          1
1 2 1          2
1 3 5          1
1 3 4          2
1 3 3          3
1 4 5          1

6 rows selected

　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码