Blog Post

RANKING Function in plain SQL

,

Below examples will show that we can still write a pure sql query for RANK, DENSE_RANK and ROW_NUMBER without using RANK() function.

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

Original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating

Blog Post

RANKING Function in plain SQL

,

Below examples will show that we can still write a pure sql query for RANK, DENSE_RANK and ROW_NUMBER without using RANK() function.

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating