Link to home
Create AccountLog in
Avatar of Tricia McKeating
Tricia McKeatingFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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

Avatar of Tricia McKeating

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
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)
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Many thanks ...excellent response as usual
Glad to help out :)