-- Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.
USE master
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name='LeetCode')
BEGIN
DROP DATABASE LeetCode
END
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO
IF EXISTS(SELECT * FROM sys.objects WHERE name='Scores')
DROP TABLE Scores
GO
CREATE TABLE Scores
(
Id INT IDENTITY PRIMARY KEY,
Score float
)
INSERT INTO Scores VALUES
(3.50), (3.65), (4.00), (3.85), (4.00), (3.65)
GO
SELECT *
FROM Scores
GO
SELECT *, ROW_NUMBER() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores
SELECT *, RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores
SELECT *, DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores
SELECT *, NTILE(5) OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores
