Sequence Number functions provided by Oracle

Sequence Number functions provided by Oracle

Take the EMP table as an example:
1: rownum is the simplest sequence number, but the value is determined before order.
Select rownum, T. * from EMP t order by ename
Row rownum empno ename job Mgr hiredate Sal comm deptno
1 11 7876 Adams clerk 7788 1987-5-23 1100 20
2 2 7499 Allen salesman 7698 1981-2-20 1600 300 30
3 6 7698 Blake manager 7839 1981-5-1 2850 30
4 7 7782 Clark manager 7839 1981-6-9 2450 10
5 13 7902 Ford analyst 7566 1981-12-3 3000 20
6 12 7900 James Clerk 7698 1981-12-3 950 30
7 4 7566 Jones manager 7839 1981-4-2 2975 20
8 9 7839 King President 1981-11-17 5000 10
9 5 7654 Martin salesman 7698 1981-9-28 1250 1400 30
10 14 7934 Miller clerk 7782 1982-1-23 1300 10
11 8 7788 Scott analyst 7566 1987-4-19 3000 20
12 1 7369 Smith clerk 7902 1980-12-17 800 20
13 10 7844 Turner salesman 7698 1981-9-8 1500 0 30
14 3 7521 ward salesman 7698 1981-2-22 1250 500 30

2: row_number () over (order by field name 1, field name 2 ,... field name N) first sort and then confirm the sequence number.
select row_number () over (order by ename) as RM, T. * From emp t
Number of rows RM empno ename job Mgr hiredate Sal comm deptno
1 7876 Adams clerk 7788 1987-5-23 1100 20
2 2 7499 Allen salesman 7698 1981-2-20 1600 300 30
3 3 7698 blke manager 7839 1981-5-1 2850 30
4 4 7782 Clark manager 7839 1981-6-9 2450 10
5 5 7902 Ford analyst 7566 1981-12-3 3000 20
6 6 7900 James Clerk 7698 1981-12-3 950 30
7 7566 Jones manager 7839 1981-4-2 2975 20
8 8 7839 King President 1981-11-17 5000 10
9 9 7654 Martin salesman 7698 1981-9-28 1250 1400 30
10 10 7934 Miller clerk 7782 1982-1-23 1300 10
11 11 7788 Scott analyst 7566 1987-4-19 3000 20
12 12 7369 Smith clerk 7902 1980-12-17 800 20
13 13 7844 Turner salesman 7698 1981-9-8 1500 0 30
14 14 7521 ward salesman 7698 1981-2-22 1250 500 30

3: The difference between partition and partition is that partition. obtain the serial number within each small partition.
select row_number () over (partition by deptno order by SAL) as RM, T. * From emp t
Number of rows RM empno ename job Mgr hiredate Sal comm deptno
1 7934 Miller clerk 7782 1982-1-23 1300 10
2 2 7782 Clark manager 7839 1981-6-9 2450 10
3 3 7839 King President 1981-11-17 5000 10
4 1 7369 Smith clerk 7902 1980-12-17 800 20
5 2 7876 Adams clerk 7788 1987-5-23 1100 20
6 3 7566 Jones manager 7839 1981-4-2 2975 20
7 4 7788 Scott analyst 7566 1987-4-19 3000 20
8 5 7902 Ford analyst 7566 1981-12-3 3000 20
9 1 7900 James Clerk 7698 1981-12-3 950 30
10 2 7521 ward salesman 7698 1981-2-22 1250 500 30
11 3 7654 Martin salesman 7698 1981-9-28 1250 1400 30
12 4 7844 Turner salesman 7698 1981-9-8 1500 0 30
13 5 7499 Allen salesman 7698 1981-2-20 1600 300 30
14 6 7698 Blake manager 7839 1981-5-1 2850 30

4: rank () indicates the number of employees in each department.
select rank () over (partition by deptno order by SAL) as rnk, T. * From emp t;
Number of rows rnk empno ename job Mgr hiredate Sal comm deptno
1 7934 Miller clerk 7782 1982-1-23 1300 10
2 2 7782 Clark manager 7839 1981-6-9 2450 10
3 3 7839 King President 1981-11-17 5000 10
4 1 7369 Smith clerk 7902 1980-12-17 800 20
5 2 7876 Adams clerk 7788 1987-5-23 1100 20
6 3 7566 Jones Manager 7839 1981-4-2 2975 20
7 4 7788 Scott analyst 7566 1987-4-19 3000 20
8 4 7902 Ford analyst 7566 1981-12-3 3000 20
9 1 7900 James Clerk 7698 1981-12-3 950 30
10 2 7521 ward salesman 7698 1981-2-22 1250 500 30
11 2 7654 Martin salesman 7698 1981-9-28 1250 1400 30
12 4 7844 salesturner man 7698 1981-9-8 1500 0 30
13 5 7499 Allen salesman 7698 1981-2-20 1600 300 30
14 6 7698 blke manager 7839 1981-5-1 2850 30

5: In this example, the only difference between dense_rank () and rank () Is that
dense_rank () is followed by the third name after the second name.
rank () is tied for the second place, followed by the fourth place.
select dense_rank () over (partition by deptno order by SAL) as dense_rnk, T. * From emp t;
Number of rows dense_rnk empno ename job Mgr hiredate Sal comm deptno
1 7934 Miller clerk 7782 1982-1-23 1300 10
2 2 7782 Clark manager 7839 1981-6-9 2450 10
3 3 7839 King President 1981-11-17 5000 10
4 1 7369 Smith clerk 7902 1980-12-17 800 20
5 2 7876 Adams clerk 7788 1987-5-23 1100 20
6 3 7566 Jones Manager 7839 1981-4-2 2975 20
7 4 7788 Scott analyst 7566 1987-4-19 3000 20
8 4 7902 Ford analyst 7566 1981-12-3 3000 20
9 1 7900 James Clerk 7698 1981-12-3 950 30
10 2 7521 ward salesman 7698 1981-2-22 1250 500 30
11 2 7654 Martin salesman 7698 1981-9-28 1250 1400 30
12 3 7844 salesturner man 7698 1981-9-8 1500 0 30
13 4 7499 Allen salesman 7698 1981-2-20 1600 300 30
14 5 7698 blke manager 7839 1981-5-1 2850 30