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

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

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

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)

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tricia McKeating

ASKER

Many thanks ...excellent response as usual

mbizup

Glad to help out :)