Add an SQL statement for the ranking column (when the column values to be ranked are equal, the ranking is the same)

When SQL queries data, a ranking column is added after sorting by a column. If the column values to be ranked are the same, the ranking column array is 9, 9, 8, 7, 7, and 6.

The added ranking column array must be displayed in two ways:

First:, (this ranking method should be the most common ranking method) or

Type 2:, (required in some special cases)

 

-- Now let's assume the test data: create a temporary table # Score and insert data.

Create Table # Score (ID int, points float) -- ID is the student ID and points is the score
insert # Score select 1, 90
Union all select 2, 85
Union all select 3, 83
Union all select 4, 85
Union all select 5, 92

-- The first rank shown in the test is as follows:
Select
Points,
(Select count (1) + 1 from # Score where points> A. Points) as ranking
From # Score a order by ranking

 
-- The result is as follows:
/*

Points Ranking
92.0 1
90.0 2
85.0 3
85.0 3
83.0 5
*/Meets the requirements.

 

-- The test shows the second ranking. The SQL statement is as follows:
Select
Points,
(Select count (distinct points) + 1 from # Score where points> A. Points) as ranking
From # Score
Order by ranking

-- Result
/*

Points Ranking
92.0 1
90.0 2
85.0 3
85.0 3
83.0 4
*/Meets the requirements.