Use of the Row_number () over () analysis function in ORACLE the ___ function

Use of the Row_number () over () analysis function in ORACLE

Row_number () over (partition by col1 ORDER by col2) is grouped according to col1, sorted internally by col2, and the value of this function represents the sequential number after each group's internal ordering (the group is contiguous and unique).

Example:

Sql> DESC T1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID number
NAME VARCHAR2 (10)
DATE1 DATE

Sql> SELECT * from T1;
ID NAME DATE1
---------- ------------------------------ ------------------
AAA 09-SEP-13

BBB 10-sep-13

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
---------- ------------------------------ ------------------ ----------
CCC 11-SEP-13 1
BBB 10-SEP-13 2
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 FR OM T1) T WHERE t.rn=1;

        ID name                            DATE1
----------------------------------------------------------
       CCC                                    11-sep-13
        102 eee                                    11-sep-13