troubleshooting Question

Produce a ranked list by group

Avatar of tmckeating
tmckeating asked on
Microsoft AccessMicrosoft Applications
6 Comments1 Solution247 ViewsLast Modified:
Hi,

Last week I asked how to produce a top 10 list of pupils based on thier number of merits and very helpfully the SQL which produced the desired result was

SELECT TOP 10 s.ID, s.Total, DCount("*","pmerits","Total > " & s.Total)+1 AS [Position]
FROM pmerits AS s
ORDER BY s.Total DESC;

I have just realised that this is giving me the top 10 in the school and what I want is the top 10 in each year group so I would need it grouped by year where year would be S1, S2 > S6

Can i just change the sql above or will I need to produce a query for each year group first.

Thanks

TMcK
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros