Selecting Top 'N' records within subgroups in a query (2 Viewers)

GoodyGoody · Oct 5, 2019

Hi Guys, I want to calculate the top 'N' results of each runner within a series of races. I have the following SQL which nicely generates the valid runners (those who have run the requisite number of races in the series):

SELECT Q.SeriesName, Q.DateFrom, Q.DateTo, Q.RaceRunner, Q.RaceTime, Q.RaceRunnerNumber, Q.RacePosition, Q.RaceCategory, Q.RaceGender, Q.RaceGenderPosition, Q.RaceRunnerFullName, Q.[Club Name], Q.HowManyRaces
FROM qryRaceSeriesResults AS Q INNER JOIN (select RaceRunner, count(*) AS [QualifyingRaces] from qryRaceSeriesResults
GROUP BY Racerunner) AS R ON Q.RaceRunner = R.RaceRunner
WHERE R.QualifyingRaces >= Q.HowmanyRaces;

Of course runners may have run more than the number of qualifying races so I only want to include the top 'N' results FOR EACH RUNNER not for the whole recordset. If I just use SELECT TOP 'N' I just the get the overall TOP 'N' results but I want the TOP 'N' for EACH runner in the recordset. Can I do this in SQL or do I have to resort to VBA?

Thanks as ever for all your great help. I am using Access 2013.
Stephen

isladogs · Oct 5, 2019

You need to use a subquery to do this.
Allen Browne covers the top N per group idea at http://allenbrowne.com/subquery-01.html#TopN

Hope that helps you work out the answer for yourself. If not, get back to us

GoodyGoody · Oct 6, 2019

Thanks IslaDogs. Ok, so I am just getting the TOP 2 results of ALL runners rather than the top 2 results for each runner. Note of course that the time is not necessarily unique unlike the OrderID in the example you sent. Here's the SQL:

SELECT * FROM qrySeriesPositionOverallIndividual AS Q
WHERE Q.RaceTImeSecs IN
(SELECT TOP 2 RaceTimeSecs FROM qrySeriesPositionOverallIndividual AS R WHERE
R.RaceRunner = Q.RaceRunner
ORDER by R.RaceTimeSecs ASC)
ORDER BY Q.RaceRunner, Q.RaceTimeSecs

Also, is it possible to use a variable as N in TOP N? So, depending on the series criteria, the number of qualifying races differs and it would be neat to use this variable as the 'N' but I can't seem to get it to work. Access just gives a syntax error. One of the fields in the query contains the variable (Q.HowManyRaces).

Thanks
Stephen

theDBguy · Oct 6, 2019

GoodyGoody said:
Also, is it possible to use a variable as N in TOP N? So, depending on the series criteria, the number of qualifying races differs and it would be neat to use this variable as the 'N' but I can't seem to get it to work. Access just gives a syntax error. One of the fields in the query contains the variable (Q.HowManyRaces).

Thanks
Stephen

Hi Stephen. I'll let Colin answer your first question. But with regards to making the 'N' part variable; unfortunately, it's only possible if you dynamically create the SQL statement. The SQL syntax requires a literal value as N. So, to make it a variable, you'll have to employ some VBA code to change it. In other words, you can't make it a variable from the query designer.

isladogs · Oct 6, 2019

I find it easier to work with sample data when constructing subqueries.
However here's a similar example I just created using same aliases as you:

The query SQL I used was:

Code:

SELECT Q.ID, Q.Group, Q.Mark
FROM Table11 AS Q
WHERE Q.Mark In (SELECT TOP 2 Mark
FROM Table11 AS R
WHERE Q.Group=R.Group
ORDER BY R.Mark DESC)
ORDER BY Q.Group, Q.Mark DESC, Q.ID;

If tha still doesn't help, can you upload something to work with.

For your second question, here's one way you can do it using VBA

Code:

Dim N As Integer, strSQL As String, qdf As QueryDef
N = 5 'select whatever value you want here - it can be entered in a textbox if you want

strSQL = "SELECT TOP " & N & " YourTableName.* FROM YourTableName;"
 Set qdf = CurrentDb.CreateQueryDef("MyQueryDef", strSQL)
DoCmd.OpenQuery "MyQueryDef"
CurrentDb.QueryDefs.Delete qdf.Name

GoodyGoody · Oct 7, 2019

Weird. I really cannot see the difference. (btw, how do I get that nice table image into this post? Anyway, here's my raw data:
RaceRunner RaceTimeSecs
Steve 1376
Steve 1387
Sara 1616
Sara 1698
Andrew 1783
Andrew 1722
Andrew 1718
Charlotte 1927
Charlotte 1972
Sue 1769
Sue 1816
Jill 1805
Jill 1741
Jill 1769
Robin 2061
Robin 1979
Nick 1548
Nick 1525
Paul 2037
Paul 1683
Ruth 1940
Ruth 1743
Lara 1970
Lara 1918

and this gives me (using the SQL i posted earlier). Thanks for the tip on VBA. I wanted to be able to use the variable in SQL but clearly it's not possible):

RaceRunner RacetimeSecs
Steve 1376
Steve 1387

I'll happily share with you the whole App if I could do that securely without posting it on the forum.

theDBguy · Oct 7, 2019

GoodyGoody said:
Weird. I really cannot see the difference.

Hi. Maybe one slight difference is the sort order in the subquery.

Code:

SELECT * FROM qrySeriesPositionOverallIndividual AS Q
WHERE Q.RaceTimeSecs IN
(SELECT TOP 2 R.RaceTimeSecs FROM qrySeriesPositionOverallIndividual AS R WHERE
R.RaceRunner = Q.RaceRunner 
ORDER BY R.RaceTimeSecs DESC)
ORDER BY Q.RaceRunner, Q.RaceTimeSecs DESC

isladogs · Oct 7, 2019

This query works

Code:

SELECT Q.RaceRunner, Q.RaceTimeSecs
FROM qrySeriesPositionOverallIndividual AS Q
WHERE (((Q.RaceTimeSecs) In (SELECT TOP 2 RaceTimeSecs FROM qrySeriesPositionOverallIndividual AS R 
WHERE Q.RaceRunner=R.RaceRunner ORDER BY R.RaceTimeSecs)))
ORDER BY Q.RaceRunner, Q.RaceTimeSecs DESC;

EDIT
Oops. Bit slow it seems....

GoodyGoody · Oct 7, 2019

Yes, I've tried playing around with the Order By clause but to no avail. I need the ORDER to be ASC for time as I want the fastest times. But if I use DESC i just get the expected slowest 2 times. The only other difference is that my underlying table is itself a query built on various other queries and tables so a lot more complex but that shouldn't matter to SQL. It's only the results of the final query that my new Top N query is interrogating. Would putting an explicit INNER JOIN perhaps help? I copied your SQL verbatim and I get the same results. Hmmm...

theDBguy · Oct 7, 2019

GoodyGoody said:
Yes, I've tried playing around with the Order By clause but to no avail. I need the ORDER to be ASC for time as I want the fastest times. But if I use DESC i just get the expected slowest 2 times. The only other difference is that my underlying table is itself a query built on various other queries and tables so a lot more complex but that shouldn't matter to SQL. It's only the results of the final query that my new Top N query is interrogating. Would putting an explicit INNER JOIN perhaps help? I copied your SQL verbatim and I get the same results. Hmmm...

Okay, if the last sample from Colin doesn't work, would you please post a db file with just some sample data in it? Thanks.

GoodyGoody · Oct 7, 2019

Thanks IslaDogs. I can see it works in the mini DB but I get the same top 2 results ignoring the grouping.

theDBguy · Oct 7, 2019

GoodyGoody said:
Thanks IslaDogs. I can see it works in the mini DB but I get the same top 2 results ignoring the grouping.

Hi. Sounds like it's time to show us the real deal then. We just need the data.

GoodyGoody · Oct 7, 2019

Just need to know how to upload the files...I don't appear to have an option to attach files to a post.

theDBguy · Oct 7, 2019

GoodyGoody said:
Just need to know how to upload the files...I don't appear to have an option to attach files to a post.

Hi. When you reply to a post, just below the reply box, there's an area for Additional Options. Try clicking on the "Manage Attachments" button.

GoodyGoody · Oct 8, 2019

Attached a full backend DB with a cut down front end. Use SeriesID 2 or go to the Calculate Series menu and select the September 2019 series.

isladogs · Oct 8, 2019

You were already using the aliases Q & R in the underlying query so reusing them in a different way gets Access (and me) confused.
So I used X and Y instead and it now works

Code:

SELECT X.RaceRunner, X.RaceTimeSecs
FROM qrySeriesPositionOverallIndividual AS X
WHERE (((X.RaceTimeSecs) In (SELECT TOP 2 RaceTimeSecs FROM qrySeriesPositionOverallIndividual AS Y
WHERE X.RaceRunner=Y.RaceRunner ORDER BY Y.RaceTimeSecs)))
ORDER BY X.RaceRunner, X.RaceTimeSecs DESC;

GoodyGoody · Oct 8, 2019

Well spotted that man. That could be a real issue if you have several layers of SQL. A good reason to use the AS keyword sparingly and only if absolutely necessary.

isladogs · Oct 8, 2019

I agree with you. I tend to avoid aliases in saved queries as it can be very difficult to see what is going on when the results aren't what you expect.
So my query statements are often longer but to me they are clearer.

Selecting Top 'N' records within subgroups in a query (2 Viewers)

Registered User.

MVP / VIP

Registered User.

I’m here to help

MVP / VIP

Attachments

Registered User.

I’m here to help

MVP / VIP

Attachments

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Attachments

Registered User.

Attachments

MVP / VIP

Attachments

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread