How to rank in SQL

This article mainly explains "how to rank in SQL". Friends who are interested might as well take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to rank in SQL.

Let's first create a test data table Scores

WITH t AS (SELECT 1 StuID,70 Score UNION ALL SELECT 2 85 UNION ALL SELECT 3 UNION ALL SELECT 4 80 UNION ALL SELECT 5 74) SELECT * INTO Scores FROM t; SELECT * FROM Scores

The results are as follows:

1. ROW_NUMBER ()

Definition: the function of the ROW_NUMBER () function is to sort the data queried by SELECT, each with a serial number, which can not be used for student performance ranking, but is generally used for paging queries, such as the first 10 queries for 10-100 students.

1.1 ranking of students' scores

Example

SELECT ROW_NUMBER () OVER (ORDER BY SCORE DESC) AS [RANK], * FROM Scores

(hint: you can swipe the code left and right)

The results are as follows:

Here RANK is the order in which each student is ranked, and DESC is reversed according to Score.

1.2 get the second place score information SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY SCORE DESC) AS [RANK], * FROM Scores) t WHERE t.RANK=2

Results:

The idea used here is that the idea of paging query is to nest a layer of SELECT in addition to the original sql.

WHERE t.RANK > = 1 AND t.RANK