DECLARE @StudentScore TABLE
(StudentName VARCHAR(1)
,StudentScore INT)
INSERT @StudentScore VALUES ('A',85),
('B',90),
('C',90),
('D',99),
('E',88)
---RANK ----
SELECT
SS1.StudentName
,SS1.StudentScore
,(
SELECT
COUNT(*) + 1
FROM
@StudentScore SS2
WHERE
SS2.StudentScore > SS1.StudentScore) AS StudentRank
FROM
@StudentScore SS1
ORDER BY
StudentRank
--DENSE RANK --
SELECT
SS1.StudentName
,SS1.StudentScore
,StudentDenseRank = (
SELECT
(COUNT(distinct SS2.StudentScore))
FROM
@StudentScore SS2
WHERE
SS1.StudentScore <= SS2.StudentScore
)
FROM
@StudentScore SS1
ORDER BY
StudentDenseRank ASC
--ROW NUM
SELECT
SS1.StudentName
,SS1.StudentScore
,StudentRowNum = (
SELECT
COUNT(*)
FROM
@StudentScore SS2
WHERE
SS1.StudentName >= SS2.StudentName
)
FROM
@StudentScore SS1
ORDER BY
SS1.StudentName