Rank Query With Groups (Array?)

[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!

 

[KARL DEWEY]

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;

 

[dmasch]

Works perfectly. Thanks Karl!

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.

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!