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://youtu.be/9wlme8NcBC8






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