Row_number () over () analysis function usage

Row_number () over () analysis function usage 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 a skip sorting. When there are two second names, the next is the fourth (also in each group) dense_rank () is also a continuous sorting. When there are two second names, it is still followed by the third name. 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: Follow col_1 [, col_2...] sort: returns the result set after sorting. This usage is somewhat like rownum, and returns a different value for each row: select rownum, ename, job, row_number () over (order by 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 10 turner salesman 10 11 ADAMS cler11 12 12 JAMES cler12 13 13 ford analyst 13 14 MILLER cler14 if there is no partition by clause, the result set is sorted by the columns specified by order by. with row_number_test as (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 a; as we expected, row_number () returns the result of sorting by Column B and then sorts by column a to obtain the following result: 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: First follow col_n [, col_m... group, and then at each minute In the group, follow col_1 [, col_2...] sort (ascending), and finally return the result set after sorting: with row_number_test as (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 a; in this example, we first group by column c and divide it into two groups. ('*' Group, '+' group), sort by Column B of each group (by the ascii code of the first character string), and finally sort by column, the following result set is obtained: a B ROW_NUMBER -- ---------- 1 one 3 4 four 2 5 five 1 13 thirteen 1 22 twenty two example: the following requirements are met, how can we extract the first three random data entries of a group that do not repeat a field? That is, group by id1 and randomly extract the first N records that are unique in id2. Example: with temp as (select 'dg 'id1, 13907551201 id2 from dual union all select 'dg' id1, 13907551201 id2 from dual union all select 'dg 'id1, 13907551201 id2 from dual union all select 'dg 'id1, 13907551204 id2 from dual union all select 'dg' id1, 13907551205 id2 from dual union all select 'dg 'id1, 13907551206 id2 from dual union all select 'dg 'id1, 13907551207 id2 from dual union all select 'dg' id1, 13 907551207 id2 from dual union all select 'DC 'id1, 13907551209 id2 from dual union all select 'DC' id1, 13907551210 id2 from dual union all select 'DC 'id1, 13907551210 id2 from dual union all select 'DC 'id1, 13907551212 id2 from dual) Implementation Method: with temp as (select 'dg' id1, 13907551201 id2 from dual union all select 'dg 'id1, 13907551201 id2 from dual union all select 'dg' id1, 13907551201 id2 from dual Union all select 'dg 'id1, 13907551204 id2 from dual union all select 'dg' id1, 13907551205 id2 from dual union all select 'dg 'id1, 13907551206 id2 from dual union all select 'dg 'id1, 13907551207 id2 from dual union all select 'dg' id1, 13907551207 id2 from dual union all select 'dc' id1, 13907551209 id2 from dual union all select 'DC 'id1, 13907551210 id2 from dual union all select 'DC' id1, 1390755121 0 id2 from dual union all select 'dc' id1, 13907551212 id2 from dual) select * from (select temp. *, row_number () over (partition by id1 order by dbms_random.random) rid1from (select temp. * --, row_number () over (partition by id1 order by id1) rid1, row_number () over (partition by id1, id2 order by id1, id2) rn from temp) tempwhere rn = 1) where rid1 <= 3 but is there a better SQL implementation because the temp table has a large amount of data? Select * from (select temp. *, row_number () over (partition by id1 order by dbms_random.random) rid1from (select distinct id1, id2 from temp) where rid1 <= 3;