Row_number () over () function usage in DB2 () __ Database

1, Row_number () over () sorting function:

(1) Row_number () over () Group sorting function:

When the Row_number () over () function is used, the grouping within over () and the execution of the sort is later than the where group by order by.

Partition by is used to group the result set, and if it is not specified then it takes the entire result set as a grouping, unlike the aggregate function where it can return multiple records in a group, and aggregate functions typically have only one record that reflects the statistical value.

For example: Employee, sorted by department group.

[SQL] view plain copy SELECT empno,workdept,salary, Row_number () over (partition by workdept order by SALARY des  c) Rank from employee--------------------------------------000010 A00 152750 1 000110 A00 66500 2 000120 A00 49250 3 200010 A00 46500 4 200120 A00 39250 5 000020 B01 94250 1 000030 C01 98250 1 000130 C0 1 73800 2

(2) Sorting the results of the query: (No grouping)

[SQL] view plain copy SELECT empno,workdept,salary, Row_number () over (order by SALARY Desc) rank from employee   --------------------------------------000010 A00 152750 1 000030 C01 98250 2 000070 D21 96170 3 000020  B01 94250 4 000090 E11 89750 5 000100 E21 86150 6 000050 E01 80175 7 000130 C01 73800 8 000060 D11 72250 9

Row_number () over () and rownum are similar, more powerful (can be sorted from 1 in each group).

2, Rank () over () is a jumping sort, with two second names followed by the fourth place (also in each group).

[SQL] view plain copy Select Workdept,salary,rank () over (partition by workdept order by salary) as Dense_rank_or   Der from emp Order by workdept; ------------------A00 39250 1 A00 46500 2 A00 49250 3 A00 66500 4 A00 152750 5 B01 94250 1 C01 6 8420 1 C01 68420 1 C01 73800 3

3, Dense_rank () over () is a sequential sort, with two second names still followed by third. In contrast, there is no duplicate value for row_number.

[SQL]   View plain copy Select workdept,salary,dense_rank ()  over (partition  By workdept order by salary)  as dense_rank_order from emp order  by workdept;  ------------------   a00 39250   1   a00 46500   2   a00 49250   3   A00 66500    4   a00 152750  5   b01 94250   1   c01 68420   1   c01 68420   1   C01 73800    2