Introduction to the use of the four major sql ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE

1. ROW_NUMBER()

Definition: The ROW_NUMBER() function is used to sort the data queried by select, adding a serial number to each piece of data. It cannot be used to rank student scores, and is generally used for paging queries.
For example, query the first 10 queries for 10-100 students.

Examples:

1.1 Sorting students’ grades

Here number is the serial number of each student, which is desc in reverse order according to studentScore (score)

1.2 Get the second student's score information

The idea used here is the idea of ​​paging query. Add another layer select where t.number>=1 and t.number<=10 outside the original SQL to obtain the score information of the first ten students.

2. RANK()

Definition: RANK() function, as the name suggests, is a ranking function that can rank a certain field. Why is it different from ROW_NUMBER()? ROW_NUMBER() is for sorting. When there are students with the same scores, ROW_NUMBER() will sort them in sequence, and their serial numbers are different, while Rank() will not be the same, and their rankings are the same. Let’s look at the example below:

2.1 Ranking students’ grades

Here you can see how ROW_NUMBER() and RANK() are the same? Because the students' scores are different, the ranking and sorting are the same. You will see the difference if you change it below.

When two students have the same score, changes occur. RANK() is 1 2 2, while ROW_NUMBER() is still 1 2 3. This is the difference between RANK() and ROW_NUMBER().

3.DENSE_RANK()

Definition: The DENSE_RANK() function is also a ranking function. It is similar to RANK() in that it also ranks fields. So what is the difference between it and RANK()? See the example:

Examples:

The difference between DENSE_RANK() and RANK() is the continuity of ranking. DENSE_RANK() is continuous, while RANK() is a jumping ranking, so the ranking function used in general is RANK().

4.NTILE()

Definition: The NTILE() function distributes the rows in an ordered partition into a specified number of groups. Each group is numbered, starting from 1, just like what we call a 'partition', divided into several zones, and how many zones there are in a zone.

Examples:

The query was performed three times here. The first time it was divided into one 'zone', so the query result numbers were all 1. The second time it was divided into two zones, the query result was 1 1 2, which means that the first 'zone' has two numbered data, 1 1, and the second 'zone' has only the data 2.

At this point, we have finished discussing the SQL ranking issue. Next time, we will introduce some in-depth SQL ranking statements.

This is the end of this article about the use of the four major sql ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE. For more information about the use of the sql ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!