Top 'N' query conundrum

[GoodyGoody]

OK, so I'm going to try and explain this as best I can. Some of you will be a little familiar with the race timing database I am writing and have helped massively in getting it to where it is. A problem has just been highlighted when calculating the overall series' results. An athlete runs in 'n' races and those races can be in a series. Each series can have a number of races you are required to finish in order to be considered for a prize and they are the basis of your overall series score. I have a query which selects the raw results data and then another which selects only those athletes who have run the requisite number of races. Let's say there are 4 races in this series and you must have run 3. The next step is where the problem lies. Here's the SQL:

SELECT *
FROM qrySeriesPositionOverallIndividual AS X
WHERE (((X.RaceCategoryPosition) In (SELECT TOP 3 RaceCategoryPosition FROM qrySeriesPositionOverallIndividual AS Y WHERE
Y.RaceRunner = X.RaceRunner 
ORDER by Y.RaceCategoryPosition ASC)))
ORDER BY X.RaceRunner, X.RaceCategoryPosition;

I have a runner whose results in his category (RaceCategoryPosition) are 6th, 6th, 7th and 7th. So, of course, as it runs through the underlying data (qrySeriesPositionOverallIndividual) it pulls in all 4 records (because both 6 and 7 are in the top 3 results). What I need to do is have a query which just selects the top 3 results for each athlete and if result 3 and 4 just happen to be the same who cares. Ideally I would just select the top 3 results for each athlete from qrySeriesPositionOverallIndividual without complicating the whole thing with this extra step but running TOP 3 on that query just gives me the absolute TOP 3 results which isn't what I want.

Can anyone help please?

 

[June7]

Is there a unique ID field in table? Try SELECT TOP 3 ID.

 

[GoodyGoody]

There is but the problem is that qrySeriesResultsIndividual has in this case 4 lines of results 6th, 6th, 7th and 7th. As the SQL runs through each row in the underlying table qrySeriesResultsIndividual it finds 6th and 6th is in the top 3 results and so on for the next 3 results so it pulls in all 4 results not just the top 3 which is what I want. I'd just like to take the top 3 records for each athlete in qrySeriesResultsIndividual but I don't know how you would write a TOP 'n' type query to do that so I came up with this solution but missed the logic error...

 

[June7]

You sort by RaceCategoryPosition but take the TOP 3 ID. The ID values are unique so if top 3 ID are 16 22 7 after sort applied, those records will be returned.

 

[GoodyGoody]

No that won't work. There isn't a unique record ID on the incoming data but anyway the issue is that I need to check if the result in the underlying data is in the top 3 results for this athlete. The trouble is the Top 3 results contain 6,6 and 7 so as it comes across each record, each record is in the top 3 results because the 3rd and 4th results are the same number so it allows all 4 records to go through. I could do it in VBA no problem just by looping through the recordset but I believe there is a way in SQL to get the top 'n' records in a group. In this case my group is an athlete.

 

[June7]

Well, you need a field or combination of fields that can provide a unique identifier.

Import data into a table that generates a unique ID with autonumber field.

 

[GoodyGoody]

There isn't a unique ID for the line nor does there need to be for TOP 'N' that's the whole point of it. I have looked on the web and the issue I have is common but all the solutions seem to be using dates or hard-coded data and ones that aren't are using T-SQL, Oracle or some other query language which seems to do other things Access sql won't. Take the Top 'n' records for a group within a recordset: I want the top 3 positions for EACH runner Sample data would be:

Runner, Race, Position
1234, 1, 6
1234,2,6
1234,3,7
1234,4,7
5678,1,20
5678,2,21
5678,3,8
5678,4,9
4321,1,1
4321,2,2
4321,3,1

Thanks

 

[June7]

There doesn't need to be a unique ID if the data you want is unique within each group but it isn't in this case. You have already experienced what happens when values are replicated. Access can't distinguish between 7 and 7 - they rank equally, therefore it returns both and you get 4 records instead of 3.

http://allenbrowne.com/subquery-01.html#TopN

 

[isladogs]

As June said, you need a unique ID for each record and then the subquery solution will work. If your table is called Results then...

SELECT Results.Runner, Results.Position, Results.Race
FROM Results
WHERE (((Results.ID) In (SELECT TOP 3 ID                            
   FROM Results AS Dupe                              
   WHERE Dupe.Runner = Results.Runner        
   ORDER BY Dupe.Race, Dupe.Position DESC)))
ORDER BY Results.Runner, Results.Position, Results.Race;

See attached

 

[GoodyGoody]

Well, thinking about it RUNNERID + RACEID is unique for all records. By definition each runner can only race once (as far as I know! lol). Would that work?

 

[isladogs]

Every table should have a PK field. Whilst you could create a unique composite PK with those 2 fields, personally I would just add an autonumber field.
That works!
I'll leave you to test whether a composite PK would work for your query. I suspect not.

 

[June7]

I think it will work. I already stated needed a unique ID field or combination of fields.

SELECT *
FROM Results
WHERE (((Runner & Race) In (SELECT TOP 3 Runner & Race                           
   FROM Results AS Dupe                              
   WHERE Dupe.Runner = Results.Runner        
   ORDER BY Dupe.Runner & Dupe.Race, Dupe.Position)))
ORDER BY Runner, Position, Race;

 

[GoodyGoody]

OK, so it looks like that is working. I thought before jumping into the next phase I would ask you guys' advice. Now I have to run this query in VBA as the 'N' in TOP 'N' is variable and as you know it cannot be a variable in access. The final step is to produce a report from this data which has 3 grouping levels and then needs to be sorted in ascending order of the RaceCategoryPosition. The underlying data has the TOP 'N' records for each runner. This data needs sumamrising into a single total for RaceCategoryPosition and then the report needs to group by Gender, RaceCategory and then Runner summing the RaceCategoryPosition field and sorting on it. I couldn't see a way of doing this within a Report but I would have thought you could as it is ultimately just a query that's being presented nicely and running a query would be easy enough.

The other thought I had and I'd like some pointers on this is to take the recordset I now have from the TOP 'N' query and then summarise that recordset in VBA to pass to the report. That bit I am unsure of, how to take a recordset and run another query on that data to summarise and sort it as needed which is then used as the underlying data for the report.

Help and advice needed please. I have 5 reports which all have this fundamental TOP 'N' logic error in it so want to get the whole thing right this time!

Thanks a lot

 

[June7]

Options:

1. Use QueryDefs code to modify saved query object used as report RecordSource

2. Pass SQL statement to report and have report set its own RecordSource property with statement, example:

Private Sub Report_Open(Cancel As Integer)
'report is called by SampleManagement and zPre2009SamplesView forms to print list of samples
'set RecordSource to pull data from appropriate tables
Me.RecordSource = Nz(Me.OpenArgs, Me.RecordSource)
End Sub