--- Ranking -----

-- Sample Data
Create table tb (Name varchar (10), Score decimal (10, 2 ))
INSERT tb SELECT 'A', 99
Union all select 'bb', 56
Union all select 'cc', 56
Union all select 'dd', 77
Union all select 'ee ', 78
Union all select 'ff ', 76
Union all select 'gg ', 78
Union all select 'ff ', 50
GO

-- 1. Ranking generation method 1. Merge the ranking when the Score is repeated
SELECT *, Place = (select count (DISTINCT Score) FROM tb WHERE Score> = a. Score)
FROM tb
Order by Place
/* -- Result
Name Score Place
--------------------------------------------
Aa 99.00 1
EE 78.00 2
Gg 78.00 2
Dd 77.00 3
FF 76.00 4
Bb 56.00 5
HTTP flood 56.00 5
FF 50.00 6
--*/

 

-- 2. Ranking generation method 2. When the score is repeated, the ranking vacancy is retained.
Select *, place = (select count (score) from TB where score> A. Score) + 1
FROM tb
Order by Place
/* -- Result
Name Score Place
-------------------------------------------
Aa 99.00 1
Ee 78.00 2
Gg 78.00 2
Dd 77.00 4
Ff 76.00 5
Bb 56.00 6
HTTP flood 56.00 6
Ff 50.00 8
--*/