Ranking In MS Access

[Transformer]

Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

However same result can be achieved using a correlated query.

e.g. Consider a dataset of student marks below.

Students	    Marks
Tony	              34
Bob	              32
Thor	              48
Jack	              42
Tom	              41
Kate	              45
Sid	              26

Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks >= T2.Marks) AS Rank 
FROM 
      [Table] AS T2 
ORDER BY Marks DESC 

[jfreeland]

If you are ranking about and wanking with data and need to break wind or break a tie, you can do something like this which uses another variable to "break the tie" when there are more than one "Marks" with the same count.

SELECT T2.Students,
       (SELECT COUNT(T1.Marks)
        FROM [Table] AS T1
        WHERE T1.Marks > T2.Marks OR (T1.Marks = T2.Marks AND T1.ID < T2.ID)) + 1 AS Rank
FROM [Table] AS T2
ORDER BY T2.Marks DESC, T2.ID;

This YouTube video also shows how to do it:






https://www.youtube.com/@alanelston2330/featured