oracle排序语句row,ORACLE中使用row_number over()排序

from:http://blog.csdn.net/iw1210/article/details/11937085

意图：实现select top 1 * from tablename

Oracle 中的 ROW_NUMBER() OVER() 分析函数的用法

ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内是连续且唯一的)。

举例：

SQL> DESC T1;

Name                                           Null?    Type

----------------------------------------- -------- ----------------------------

ID                                                    NUMBER

NAME                                                    VARCHAR2(10)

DATE1                                                    DATE

SQL> SELECT * FROM T1;

ID NAME                           DATE1

---------- ------------------------------ ------------------

101 aaa                                  09-SEP-13

101 bbb                                  10-SEP-13

101 ccc                                  11-SEP-13

102 ddd                                  08-SEP-13

102 eee                                  11-SEP-13

SQL> SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as RN FROM T1;

ID NAME               DATE1               RN

---------- ------------------------------ ------------------ ----------

101 ccc                  11-SEP-13              1

101 bbb                  10-SEP-13              2

101 aaa                  09-SEP-13              3

102 eee                  11-SEP-13              1

102 ddd                  08-SEP-13              2

把上面语句作为一个子表语句，嵌入到另一条语句中：

SQL> SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as RN FROM T1)T WHERE T.RN=1;

ID NAME                           DATE1

---------- ------------------------------ ------------------

101 ccc                                  11-SEP-13

102 eee                                  11-SEP-13