Rank Students as per Total Marks

[svenki15]

Hi!
I have a table named StudentMarks as under:-

Roll No Name Total Mks (1000)
001 xyz 775
002 abc 665
003 pqr 500

I have created a query named StudentMarks Query with above fields. I want a
rank field as under:-

Roll No Name Total Mks (1000) Rank
001 xyz 775 1
002 abc 665 2
003 pqr 500 3

what expression should i type in the expression builder or in the Rank field
to achieve this. I have tried other suggestions with no result as i am a
complete dummy with this
please help

Venky

 

[Michel Walsh]

In an SQL view, try:



SELECT a.rollNo, a.name, a.totalMks, COUNT(*) as rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.totalMks >= b.totalMks
GROUP BY a.rollNo, a.name, a.totalMks



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Simplest expression (slow with large sets of records)

Rank: DCount("*","StudentMarks","[Total Mks]>" & [Total Mks]) + 1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

Try this ---
SELECT T.[Roll No], T.Name, T.[Total Mks (1000)], (SELECT COUNT(*)
FROM [YourTable] T1
WHERE T1.[Total Mks (1000)] <= T.[Total Mks (1000)) AS Rank
FROM [YourTable] AS T
ORDER BY T.[Roll No], T.Name,T.[Total Mks (1000)];

 

[Michel Walsh]

Use <= instead of >=.

If there are ties, the rank will be the highest rank. For { 11, 22, 22, 33,
44 ... } the ranks would be { 1, 3, 3, 4, 5, ...}. If you want a low
rank, {1, 2, 2, 4, 5, ... }, in that case, use:


SELECT a.rollNo, a.name, a.totalMks, 1+COUNT(b.rollNo) as rank
FROM tableName AS a LEFT JOIN tableName AS b
ON a.totalMks < b.totalMks
GROUP BY a.rollNo, a.name, a.totalMks



Vanderghast, Access MVP

 

[svenki15]

Simplest expression (slow with large sets of records)

Rank: DCount("*","StudentMarks","[Total Mks]>" & [Total Mks]) + 1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi!

Thanks a Ton People!!
John yours was truly the simplest and i got the results i wanted in a jiffy
though my records run upto 600 students.
Thanks again
venky

 

[svenki15]

Simplest expression (slow with large sets of records)

Rank: DCount("*","StudentMarks","[Total Mks]>" & [Total Mks]) + 1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Hi !

Thanks a million people. John ur solution was truly the simplest and it
works for my 600 students

Venky

 

[Bill]

This got me on the right track. What if I needed to have the rank done by
marks within series ( 1 to whatever by series). For instance, same example
but a fourth column that is named series.

Roll No Name Total Mks (1000) Series

 

[Michel Walsh]

SELECT a.RollNo, LAST(a.Name), LAST(a.totalMks), LAST(a.series), COUNT(*) as
rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.series=b.series AND a.totalMks<= b.totalMks
GROUP BY a.RollNo




Vanderghast, Access MVP