In-depth discussion: usage of row_number () over () analysis functions in oracle

Row_number () over (partition by col1 order by col2) indicates grouping by col1 and sorting by col2 within the group, the value calculated by this function indicates the sequential numbers after sorting in each group (the sequential and unique values in the group ).
The difference between rownum and rownum is that when rownum is used for sorting, it adds fake rownum to the result set before sorting, this function sorts the clause before calculating the row number.
Row_number () is similar to rownum and more powerful (it can be sorted from 1 in each group ).
Rank () is the Skip sorting. When there are two second names, the next is the fourth name (also in each group)
Dense_rank () is also a continuous sorting, with two second names still followed by the third. In contrast, row_number does not have repeated values.
The oracle analysis function row_number () returns an integer (> = 1 );
Syntax format:
1. row_number () over (order by col_1 [, col_2...])
Purpose: sort by col_1 [, col_2...] and return the sorted result set,
This usage is a bit like rownum, and returns a different value for each row:
Copy codeThe Code is as follows:
Select rownum, ename, job,
Row_number () over (order by rownum) row_number
From emp;
Rownum ename job ROW_NUMBER
---------------------------------------
1 SMITH cler1
2 allen salesman 2
3 ward salesman 3
4 jones manager 4
5 martin salesman 5
6. blake manager 6
7 clark manager 7
8 scott analyst 8
9 king president 9
10 turner salesman 10
11 adams clerk 11
12 JAMES cler12
13 ford analyst 13
14 miller clerk 14

If there is no partition by clause, the result set is sorted by the column specified by order;
Copy codeThe Code is as follows:
With row_number_test (
Select 22 a, 'twenty two' B from dual union all
Select 1, 'one' from dual union all
Select 13, 'thirteen' from dual union all
Select 5, 'five' from dual union all
Select 4, 'four 'from dual)
Select a, B,
Row_number () over (order by B)
From row_number_test
Order by;

As we expected, row_number () returns the result sorted by Column B,
Then sort by a to get the following result:
Copy codeThe Code is as follows:
A B ROW_NUMBER () OVER (ORDERBYB)
--------------------------------------
1 one 3
4 four 2
5 five 1
13 thirteen 4
22 twenty two 5

2. row_number () over (partition by col_n [, col_m...] order by col_1 [, col_2...])
Purpose: group data by col_n [, col_m,
In each group, sort by col_1 [, col_2...] in ascending order ),
Finally, the result set after sorting is returned:
Copy codeThe Code is as follows:
With row_number_test (
Select 22 a, 'twenty two 'B,' * 'C from dual union all
Select 1, 'one', '+' from dual union all
Select 13, 'thirteen', '*' from dual union all
Select 5, 'five', '+' from dual union all
Select 4, 'four ',' + 'from dual)
Select a, B,
Row_number () over (partition by c order by B) row_number
From row_number_test
Order by;

In this example, we first group by column c and divide it into two groups ('*' group, '+' group ),
Sort by Column B of each group (in the ascii code column of the first character of the string ),
Finally, sort by column a to obtain the following result set:
Copy codeThe Code is as follows:
A B ROW_NUMBER
----------------------
1 one 3
4 four 2
5 five 1
13 thirteen 1
22 twenty two