Solved Rank sequentially without skipping any number

[Tajaldeen]

Hi Guys,
I have a table with three fields: ID, Name, Mark
i also have created the following Query which ranks students in the table based on Mark

SELECT T2.Name, 1+(SELECT COUNT(T1.Mark)
FROM
[Table] AS T1
WHERE T1.Mark >T2.Mark) AS Rank
FROM [Table] AS T2
ORDER BY T2.Mark DESC;

the problem: if there is a duplicated mark in tow records the query will skip a number like this:
John 50 1
Mark 60 2
Ali 60 2
Pall 70 4
i dont want number 3 to be skipped so the rank will be like this: 1,2,2,3
is there any solution ?

 

[ebs17]

is there any solution ?

Where there is a will, there's a way.

SELECT
   T2.Name,
   1 +
   (
      SELECT
         COUNT(*)
      FROM
         (
            SELECT DISTINCT
               Mark
            FROM [TABLE]
               ) AS T1
      WHERE
         T1.Mark > T2.Mark) AS Rank
FROM
   [Table] AS T2
ORDER BY
   T2.Mark DESC

 

[isladogs]

Crossposted at https://www.accessforums.net/showthread.php?t=87462

 

[arnelgp]

You add Autonumber (say, ID) to your Table then create this Function:

Public Function fnRank(ByVal Table As String, FieldToRank As String, ByVal AutoFieldName As String, ByVal AutoFieldValue As Long)
    Dim i As Long
    With CurrentDb.OpenRecordset( _
        "select * From [" & Table & "] Order By [" & FieldToRank & "] Desc;", dbOpenSnapshot, dbReadOnly)
        .FindFirst "[" & AutoFieldName & "] = " & AutoFieldValue
        fnRank = .AbsolutePosition + 1
    End With
End Function

your query will look like this:

SELECT
    yourTable.Name,
    yourTable.Mark,
    fnRank("yourTable","Mark","ID",[ID]) AS Ranking
FROM yourTable
ORDER BY yourTale.Mark DESC;

 

[Tajaldeen]

Where there is a will, there's a way.

SELECT
   T2.Name,
   1 +
   (
      SELECT
         COUNT(*)
      FROM
         (
            SELECT DISTINCT
               Mark
            FROM [TABLE]
               ) AS T1
      WHERE
         T1.Mark > T2.Mark) AS Rank
FROM
   [Table] AS T2
ORDER BY
   T2.Mark DESC

Amazing .... thank you very very very much, you just solved a big problem.
where there is a will , there is a way.