Avatar of tmckeating
tmckeating
 asked on

Produce a ranked list by group

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
Microsoft ApplicationsMicrosoft Access

Avatar of undefined
Last Comment
mbizup

8/22/2022 - Mon
mbizup

With the makeup of your original query, I believe you're going to have to do this in two steps.  I'm assuming that your year field is named "yr", and that per your description it is a text field:

1. Run a Make-table query to organize the rankings by year:
SELECT s.ID, s.Total, DCount("*","pmerits","Total > " & s.Total & " AND yr = '" & s.yr & "'")+1 AS [Position], s.yr INTO tblTempTotals
FROM pmerits AS s
ORDER BY s.yr, s.Total DESC;

Open in new window



2. Run a select query on that new table to get the top N per group (10 per year):
SELECT t.ID, t.total, t.yr, t.position
FROM tblTempTotals t
WHERE t.ID in
(SELECT TOP 10 t2.ID                            
   FROM tblTempTotals AS t2                              
   WHERE t2.yr = t.yr        
   ORDER BY t2.Total DESC)

Open in new window

tmckeating

ASKER
Hi,

That looks like what I am looking for but the result from both parts looks identical ..why do I need the second select query part?

TMcK
mbizup

You probably have 10 or fewer records per year (?)

The first step orders ALL of your records.

The second reduces the recordset to the top 10 per year.

If you do have 10 or fewer records per year, to really see the difference the second part makes, temporarily replace this:
>>> SELECT TOP 10 t2.ID

With this:

SELECT TOP 3 t2.ID

(Or any number that is smaller than the actual number of records you have per year)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
tmckeating

ASKER
Many thanks ...excellent response as usual
mbizup

Glad to help out :)