Ranking without Rank()

Question

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()?

But why don't you want to use Rank()? Are you facing any performance issues? — Shikhar Arora, Oct 23 '19 at 06:55
Possible solution: https://stackoverflow.com/questions/46856267/implement-rank-without-using-analytic-function. — Zhorov, Oct 23 '19 at 07:02
*I have been given this as a task.* but you are allowed to have someone else do it for you? Or was it perhaps intended for you to work it out? — Dale K, Oct 23 '19 at 08:39

LukStorms · Answer 1 · 2019-10-23T09:07:18.153

3

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.

edited Oct 23 '19 at 09:07

answered Oct 23 '19 at 08:02

LukStorms

28,916
5
31
45

Salman A · Accepted Answer · 2019-10-23T08:49:01.783

1

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

edited Oct 23 '19 at 08:49

answered Oct 23 '19 at 08:21

Salman A

262,204
82
430
521

score 0 · Answer 3 · answered Oct 23 '19 at 07:45

I am creating temporary table with identity key in order to sort the rows. When an INSERT is performed against table with IDENTITY column, the ORDER BY clause is respected by the SQL engine.

Then, I am using recursive CTE to create a RANK column for each row. The idea is simple:

if the class is changed, restart the ranking
if the class is the same and marks the same - use same rank (increase counter of current rank)
if the classis the same and the marks are not, increase the rank with 1 and reset the counter of rank

We are using such counter in order to get implement RANK behavior, for DENSE_RANK we do not need such counter.

So, the code is like this:

DECLARE @DataSource TABLE
(
    [StudID] INT
   ,[Name] VARCHAR(12)
   ,[Class] VARCHAR(12)
   ,[Marks] TINYINT
);

INSERT INTO @DataSource ([StudID], [Name], [Class], [Marks])
VALUES ('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');

CREATE TABLE #DataSource
(
    [StudID] INT
   ,[Name] VARCHAR(12)
   ,[Class] VARCHAR(12)
   ,[Marks] TINYINT
   ,[RowID] INT IDENTITY(1,1)
)

INSERT INTO #DataSource ([StudID], [Name], [Class], [Marks])
SELECT [StudID], [Name], [Class], [Marks]
FROM @DataSource
ORDER BY [Class] ASC, [Marks] DESC;

WITH DataSource AS
(
    SELECT *
          ,1 AS [Rank]
          ,0 AS [RanksCount]
    FROM #DataSource
    WHERE [RowID] = 1
    UNION ALL
    SELECT DS1.*
          ,CASE WHEN DS1.[Class] = DS2.[Class] 
                THEN CASE WHEN DS1.[Marks] = DS2.[Marks] THEN DS2.[Rank] ELSE DS2.[Rank] + DS2.[RanksCount] + 1 END
                ELSE 1
           END
          ,CASE WHEN DS1.[Class] = DS2.[Class] 
                THEN CASE WHEN DS1.[Marks] = DS2.[Marks] THEN DS2.[RanksCount] + 1 ELSE 0 END
                ELSE 0
           END
    FROM #DataSource DS1
    INNER JOIN DataSource DS2
        ON DS1.[RowID] = DS2.[RowID] + 1

)
SELECT *
FROM DataSource
ORDER BY [RowID];


DROP TABLE #DataSource;

Note, this is an idea. You can replace the CASE WHEN statements with IIF or you can write the CTE in different way (without using second table for storing the data).

Good luck.

score 0 · Answer 4 · answered Oct 23 '19 at 07:46

The following works with count only although you're better off using rank:

COUNT(*) OVER (PARTITION BY Class ORDER BY Marks ASC RANGE BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW)
    - COUNT(*) OVER (PARTITION BY Class,Marks) + 1

Alternatively you could use a correlated sub query, this uses just a pure count but will be slower:

(SELECT COUNT(*) FROM StudentTable AS CountMe WHERE StudentTable.Class = CountMe.Class AND StudentTable.Marks > CountMe.Marks) + 1 AS Rank

Ranking without Rank()

4 Answers4

Linked