Usage of the row_number () over () analysis function in ORACLE

Usage of the row_number () over () analysis function in ORACLE

Row_number () over (partition by col1 ORDER by col2) indicates that sorting is based on col1 within the grouping according to Col2 groupings, and that the value computed by this function represents the sequential number of each set of internally ordered (contiguous and unique within the group).

Example:

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

Embed the above statement as a child table statement in another statement:

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

Usage of the row_number () over () analysis function in ORACLE