Row_number () over (partition by col1 ORDER by col2) is grouped according to col1, sorted by col2 within the group, and the value of this function represents the sequential number of the internal sort in each group (a sequential unique within the group).
The difference between the rownum and the rownum is that the result set is first sorted by adding a fake rownum and then sorted, and the function is sorted before the line number is counted after the sort clause is included.
Row_number () is about the same as rownum, which is a bit more powerful (you can sort from 1 in each group).
Rank () is a jump sort, with two second names followed by fourth place (also within each group)
Dense_rank () is also a sequential sort, with two second names still followed by third. In contrast, there is no duplicate value for row_number.
Oracle Parse function row_number (), returns an integer value (>=1);
Syntax Format:
1.row_number () over (order by col_1[,col_2 ...])
Function: According to col_1[,col_2 ...] Sort, returning the sorted result set,
This usage is a bit like rownum, which returns a different value for each row:
Copy Code code as follows:
Select Rownum,ename,job,
Row_number () over (rownum) row_number
from EMP;
rownum ename JOB Row_number
---------- ---------- --------- ----------
1 SMITH Clerk 1
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
TURNER salesman 10
ADAMS Clerk 11
JAMES Clerk 12
FORD ANALYST 13
MILLER Clerk 14
If there is no partition by clause, the result set is sorted according to the column specified by the order;
Copy Code code as follows:
With Row_number_test as (
Select a, ' Twenty two ' B from dual UNION ALL
Select 1, ' One ' from dual union ALL
Select, ' 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 A;
As we expected, row_number () returns the results sorted by column B,
And then sort by a to get the following results:
Copy Code code as follows:
A B row_number () over (ORDERBYB)
-- ---------- --------------------------
1 One 3
4 Four 2
5 Five 1
Thirteen 4
Twenty two 5
2.row_number () over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
Function: First, according to Col_n[,col_m ... To group,
Then in each group according to the col_1[,col_2 ...] To sort (ascending),
Finally, it returns the result set after the sequence is arranged:
Copy Code code as follows:
With Row_number_test as (
Select a, ' Twenty two ' B, ' * ' C from dual union all
Select 1, ' One ', ' + ' from dual UNION ALL
Select, ' 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 A;
In this example, we first grouped into 2 groups (' * ' groups, ' + ' groups) in Group C,
Sorted by column B in each group (ASCII code in the first letter of the string),
Finally, sort by column A to get the following result set:
Copy Code code as follows:
A B Row_number
-- ---------- ----------
1 One 3
4 Four 2
5 Five 1
Thirteen 1
Twenty Two
