Imagine you have a school full of Students and they have done a variety of exams. All results are collected in a table and you would like to obtain rankings by subject. How can you automatically rank all the students for whom you have results.
The table T01Student
PKID Students Marks Subject
1 Tony 34 Maths
2 Bob 32 Maths
3 Thor 48 Maths
4 Jack 42 Geography
5 Tom 41 Geography
6 Kate 45 Geography
7 Sid 26 Geography
8 Michael 40 Chemistry
9 Colin 50 Chemistry
10 Hannah 60 Chemistry
11 Geoff 5 Chemistry
12 Jim 2 Chemistry
It is then possible to use the following query to get a ranking
SELECT (select count(*) from T01Student as tbl2 where T01Student.marks < tbl2.marks and T01Student.subject = tbl2.subject)+1 AS rank, * INTO TempRank FROM T01Student;
rank PKID Students Marks Subject
2 1 Tony 34 Maths
3 2 Bob 32 Maths
1 3 Thor 48 Maths
2 4 Jack 42 Geography
3 5 Tom 41 Geography
1 6 Kate 45 Geography
4 7 Sid 26 Geography
3 8 Michael 40 Chemistry
2 9 Colin 50 Chemistry
1 10 Hannah 60 Chemistry
4 11 Geoff 5 Chemistry
5 12 Jim 2 Chemistry
Then use a simple select query to order by subject then rank – Note Depending if you want to count down from the top so the lowest “Marks” gets the highest rank reverse the < symbol or reverse the order of rank - here I have highest mark is no 1. Subject rank Students Marks Chemistry 1 Hannah 60 Chemistry 2 Colin 50 Chemistry 3 Michael 40 Chemistry 4 Geoff 5 Chemistry 5 Jim 2 Geography 1 Kate 45 Geography 2 Jack 42 Geography 3 Tom 41 Geography 4 Sid 26 Maths 1 Thor 48 Maths 2 Tony 34 Maths 3 Bob 32 If for some reason you are wanting to store the rank so that you can artificially alter the ranking then it would be possible to use make table to create a new table with the ranking and then update a position field with the rank in the ranking query based on the PKID