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

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