Oracle grouping and sorting functions

Oracle grouping and sorting functions

In project development, we sometimes encounter the situation that requires grouping and sorting to solve the problem:
1. It is required that field1 be grouped and sorted by field2 in each group;
2. It is also required to retrieve the data of the first few rows sorted by group in 1.

Here, we use a table example and SQL statement to describe the usage of grouping and sorting functions in oracle databases.
1. row_number () over ()
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 ).

2. rank () over ()
Rank () is the Skip sorting. When there are two second names, the next is the fourth name (also in each group)

3. dense_rank () over ()
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.

Example:
If the table Test is available, the data is as follows:
SQL code:
CREATEDATE ACCNO MONEY
111 200
111 600
111 400
111 300
222 200
222 800
222 500
222 100
333 800
333 500
333 200
333 0

For example, if you want to group by ACCNO and each group is sorted by CREATEDATE, the group is sorted, not all data is sorted in a unified manner,
Use the following statement:

SQL code
Select t. *, row_number () over (partition by accno order by createDate) row_number from Test t

The query result is as follows:


You can note that there are two identical CREATEDATE records with ACCNO 111. Using the row_number function, their Group counts are continuous and unique. However, if the rank or dense_rank function is used, the effect is different,

As follows:
Rank SQL:
Select t. *, rank () over (partition by accno order by createDate) rank from Test t

Query results:

We can find that the two records with the same CREATEDATE are two 2nd records, followed by 4th.

Dense_rank SQL:
SQL code
Select t. *, dense_rank () over (partition by accno order by createDate) dense_rank from Test t

Query results:

We can find that when the two fields with the same CREATEDATE are two 2nd fields, the next step is 3rd.

Special Business Requirements in the project may be required to use three different functions.


For example, you may want to retrieve the data records of each group after grouping and sorting. The SQL statement is as follows:
SQL code
Select createDate, accno, money, row_number from (select t. *, row_number () over (partition by accno order by createDate) row_number from Test t) t1 where row_number <4


The query result is as follows: