Rank Query With Groups (Array?)

  • Thread starter Thread starter dmasch
  • Start date Start date
D

dmasch

Hello, I have a table of data with grades which are grouped by study. I need
to rank the grades relative to all the other grades in the same study. Below
is an example the study and grade fields with the desired outcome for the
rank.
Study Grade Rank
11111 89 1
11111 75 2
11111 65 3
22222 99 1
22222 87 2
Hopefully this can be done with simple SQL in a query and not with code but
I am open to anything that works. Many thanks in advance for your time and
expertise!
 
Try this --
SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE
YourTable.Study = [XX].Study AND YourTable.Grade <= [XX].Grade) AS Rank
FROM YourTable
ORDER BY Study, Grade DESC;
 
Works perfectly. Thanks Karl!

KARL DEWEY said:
Try this --
SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE
YourTable.Study = [XX].Study AND YourTable.Grade <= [XX].Grade) AS Rank
FROM YourTable
ORDER BY Study, Grade DESC;

--
Build a little, test a little.


dmasch said:
Hello, I have a table of data with grades which are grouped by study. I need
to rank the grades relative to all the other grades in the same study. Below
is an example the study and grade fields with the desired outcome for the
rank.
Study Grade Rank
11111 89 1
11111 75 2
11111 65 3
22222 99 1
22222 87 2
Hopefully this can be done with simple SQL in a query and not with code but
I am open to anything that works. Many thanks in advance for your time and
expertise!
Click to expand...
Click to expand...
 
Back
Top