Q:

Ranking without Rank()

Here is the table:
StuId   Name    Class   Marks
-----------------------------
2003    aman    X-A     91
2005    ankita  X-A     89
2010    Aakash  X-A     87
2011    Cyril   X-A     87
2012    Bala    X-B     87
2013    Sara    X-C     89
2014    Katlyn  X-C     89
2015    Casy    X-C     87
2016    Katie   X-B     93

I need output table to be:
StuId   Name    Class   Marks Rank
-----------------------------------
2003    aman    X-A     91    1
2005    ankita  X-A     89    2
2010    Aakash  X-A     87    3
2011    Cyril   X-A     87    3
2016    Katie   X-B     93    1
2012    Bala    X-B     87    2
2013    Sara    X-C     89    1
2014    Katlyn  X-C     89    1
2015    Casy    X-C     87    3

For which I executed the following query:
SELECT *,
    RANK() OVER (PARTITION BY Class ORDER BY Marks DESC) AS Rank
FROM StudentTable;

But how do I get the same result without using Rank()?

A:

You could try with a correlated subquery that uses a count distinct for marks higher or equal in the same "Class".
SELECT *, 
(
 SELECT COUNT(DISTINCT s2.Marks) 
 FROM StudentTable s2 
 WHERE s2.Class = s.Class 
   AND s2.Marks >= s.Marks
) AS Rank
FROM StudentTable s
ORDER BY Class, Marks DESC;

A test db<>fiddle can be found here
But RANK will be more efficient.

A:

In words, the rank of the student would be the number of students who have higher score than hir plus one. E.g. the students with 87 marks have 89 and 91 before them so they are ranked 3:
SELECT t.*, (
    SELECT COUNT(*)
    FROM StudentTable AS x
    WHERE x.Class = t.Class
    AND x.Marks > t.Marks
) + 1 AS Rank
FROM StudentTable AS t
ORDER BY t.Class, Rank

SQL Fiddle

