TOP 10 Subquery not working

[ambulocetus27]

I have seen many other posts on this forum relating to TOP n Subqueries, but unfortunately none have helped to solve my issue.

I have a race league, I want to select the top 10 point scores for each member.

I have read the Allen Browne article (and many others) and tried many variations on his code but cannot get this working.

I face two issues
- The ORDER by clause has no effect, points are not sorted with largest first
- Access being unable to differentiate between scores with the same value and returning additional records. I have added an "Event" field to make the record unique, but this does not seem to help.

Query code is

SELECT qLeague.Member, qLeague.Event, qLeague.Points
FROM qLeague
WHERE qLeague.Points IN
(SELECT TOP 10 Points
FROM qLeague AS Dupe
WHERE Dupe.Points= qLeague.Points
ORDER BY Dupe.Member, Dupe.Points DESC
)
ORDER BY qLeague.Member ASC, qLeague.Points;


This returns more than 10 results per member:

Member Event Points
Alex Peters SDMC North Weald Sprint 3
Alex Peters HCAAC Debden May 3
Alex Peters GB/Harrow TAMS NW Sprint 4
Alex Peters HCAAC Debden Sprint 5
Alex Peters Llys y Fran Hillclimb 6
Alex Peters B&H Goodwood Bailey sprint 7
Alex Peters AMC Abingdon Sprint 7
Alex Peters SDMC North Weald Sprint 7
Alex Peters Llandow Sprint 8
Alex Peters TWMC Goodwwod 9
Alex Peters SEMSEC Brands Hatch Sprint 9
Alex Peters RMC Lydden Sprint 11
Alex Peters Mira Sprint 13
Alex Peters TWMC Lydden Sprint April 15
Andy Webber Barc Mallory 9
Andy Webber SDMC North Weald Sprint 11
Andy Webber B19 May Hethel Sprint 11
Andy Webber GB/Harrow TAMS NW Sprint 11
Andy Webber TWMC Lydden Sprint April 12
Andy Webber HCAAC Debden Sprint 12
Andy Webber HCAAC Debden May 12
Andy Webber SDMC North Weald Sprint 13
Andy Webber Llandow Sprint 14
Andy Webber AMC Abingdon Sprint 14
Andy Webber Llys y Fran Hillclimb 15
Bob Hoare RMC Lydden Sprint 6
Bob Hoare SDMC North Weald Sprint 6
Bob Gibson HCAAC Debden May 14
Bob Gibson HCAAC Debden Sprint 15

etc...

 

[jdraw]

I took your published data as my test data.
Created a table called LeagueInfo. I added an autonumber PK called ID.
To remove replicate scores for individuals I used Distinct .
To get the results by Member, and Points descending - use
ORDER BY LeagueInfo.Member, LeagueInfo.Points DESC

The following is the query used

SELECT distinct LeagueInfo.Member, LeagueInfo.Points
FROM LeagueInfo
WHERE ( LeagueInfo.Points) In 
  (SELECT TOP 10 Points
   FROM LeagueInfo AS Dupe
   WHERE Dupe.ID= LeagueInfo.ID
   ORDER BY Dupe.Member  
   )
ORDER BY LeagueInfo.Member, LeagueInfo.Points DESC;

The query result is

Member	Points
Alex Peters	15
Alex Peters	13
Alex Peters	11
Alex Peters	9
Alex Peters	8
Alex Peters	7
Alex Peters	6
Alex Peters	5
Alex Peters	4
Alex Peters	3
Andy Webber	15
Andy Webber	14
Andy Webber	13
Andy Webber	12
Andy Webber	11
Andy Webber	9
Bob Gibson	15
Bob Gibson	14
Bob Hoare	6

 

[ambulocetus27]

Thank you very much for your kind help.

I apologise that I hadn't explained the problem sufficiently clearly to do with duplicate scores - the desired result is to select the top 10 highest scores (even if they are the same value).

My problem with access is that it counts all scores with the same value as a single count in the top 10. (i.e. nine instances of points=5 would be counted as a single entry in the top 10 list).

Unfortunately the DISTINCT statement removes the duplicate scores.

I would be grateful if you could offer any further advice.

 

[jdraw]

If you don't use Distinct, you will get the 12 or more records. The result will include the multiple values.

I'm confused - in you original post you said -

I face two issues
- The ORDER by clause has no effect, points are not sorted with largest first
- Access being unable to differentiate between scores with the same value and returning additional records. I have added an "Event" field to make the record unique, but this does not seem to help.

The Points DESC in the outside Order By
resolved that, right?

Access being unable to differentiate between scores with the same value and returning additional records. Do you want the additional records? If yes, then remove Distinct.

If I have misunderstood, please advise and povide sample.

 

[ambulocetus27]

Apologies for the confusion, to clarify the following are the expected results for member "Alex Peters" - ten values including the highest point duplicates

Alex Peters 15
Alex Peters 13
Alex Peters 11
Alex Peters 9
Alex Peters 9
Alex Peters 8
Alex Peters 7
Alex Peters 7
Alex Peters 7
Alex Peters 6

 

[jdraw]

Try this

SELECT  LeagueInfo.Member, LeagueInfo.Points 
FROM LeagueInfo
WHERE ( LeagueInfo.id) In 
  (SELECT TOP 10 id
   FROM LeagueInfo AS Dupe
   WHERE Dupe.Member = LeagueInfo.member
   ORDER BY Dupe.Member  
   )
ORDER BY LeagueInfo.Member, LeagueInfo.Points DESC;

 

[ambulocetus27]

Thank you for your continued help, however, the TOP 10 function does not seem to filter out any records (i.e. 15 are displayed for the first member).

The code used is:

SELECT qLeague.Member, qLeague.Points
FROM qLeague
WHERE qLeague.ResultsID In
(SELECT TOP 10 ResultsID
FROM qLeague AS Dupe
WHERE Dupe.Member = qLeague.Member
ORDER BY Dupe.Member )
ORDER BY qLeague.Member, qLeague.Points DESC;

Note the ResultsID is the index taken from the source query. is this what you had intended ?

 

[jdraw]

Look at my sample. I added field ID an autonumber PK, and adjusted the SQL accordingly.

SELECT LeagueInfo.Member, LeagueInfo.Points
FROM LeagueInfo
WHERE ( LeagueInfo.id) In 
  (SELECT TOP 10 id
   FROM LeagueInfo AS Dupe
   WHERE Dupe.Member = LeagueInfo.member
   ORDER BY Dupe.Member  
   )
ORDER BY LeagueInfo.Member, LeagueInfo.Points DESC;

Here are the results.

Member	Points
Alex Peters	15
Alex Peters	13
Alex Peters	11
Alex Peters	9
Alex Peters	9
Alex Peters	8
Alex Peters	7
Alex Peters	7
Alex Peters	7
Alex Peters	6
Andy Webber	15
Andy Webber	14
Andy Webber	14
Andy Webber	13
Andy Webber	12
Andy Webber	12
Andy Webber	12
Andy Webber	11
Andy Webber	11
Andy Webber	11
Bob Gibson	15
Bob Gibson	14
Bob Hoare	6
Bob Hoare	6

 

[ambulocetus27]

Jdraw,

thanks to your help I have got the query working. I added an ORDER BY argument - Dupe.Points DESC this seemed to fix it - I would have expected it to affect the order of the output records, not the number - mmmm. The code is below.

SELECT qLeague.Member, qLeague.Points
FROM qLeague
WHERE qLeague.ResultsID In
(SELECT TOP 10 ResultsID
FROM qLeague AS Dupe
WHERE Dupe.Member = qLeague.Member
ORDER BY Dupe.Member, Dupe.Points DESC )
ORDER BY qLeague.Member, qLeague.Points DESC;

However, I find the behaviour of access completely inscrutable. The following avriation of the above query adds an additional Boolean field [Local Trophy]. Filtering on a TRUE condition only returns 5 results (in a TOP 6 query). I suspect it is applying the TOP 6 before the [local trophy] = TRUE. How can I change this ? Code is below:

SELECT qLeague.Member, qLeague.Points, qLeague.[Local Trophy]
FROM qLeague
WHERE (((qLeague.[Local Trophy])=True) AND ((qLeague.ResultsID) In (SELECT TOP 6 ResultsID
FROM qLeague AS Dupe
WHERE Dupe.Member = qLeague.Member
ORDER BY Dupe.Member, Dupe.Points DESC )))
ORDER BY qLeague.Member, qLeague.Points DESC;

 

[jdraw]

Glad you have it working, but the

I would have expected it to affect the order of the output records, not the number - mmmm

is incorrect in my view.

You could try the latest sql I provided along with the result set.
Is the result I provided not what you were trying to achieve?

 

[ambulocetus27]

Again, apologies for the confusion. The problem for the original data set is solved.

I now need a number of variations on this query, where a subset of the results is required for a number of different championships, each indicated by a flag (local trophy, tourist trophy, hill climb trophy, etc). The problem appears to be that the "TOP 6" function is evaluated before the flag. Therefore only 5 records are returned for member "Alex Peters". Is it possible to have a nested WHERE (rather than the current AND) to achieve this ?

current query:

SELECT qLeague.Member, qLeague.Points, qLeague.[Local Trophy]
FROM qLeague
WHERE (((qLeague.[Local Trophy])=True) AND ((qLeague.ResultsID) In (SELECT TOP 6 ResultsID
FROM qLeague AS Dupe
WHERE Dupe.Member = qLeague.Member
ORDER BY Dupe.Member, Dupe.Points DESC )))
ORDER BY qLeague.Member, qLeague.Points DESC;

Raw data:

Member Points Local Trophy
Alex Peters 15 Yes
Alex Peters 3 Yes
Alex Peters 7 Yes
Alex Peters 4 Yes
Alex Peters 0 No
Alex Peters 9 Yes
Alex Peters 8 No
Alex Peters 6 No
Alex Peters 13 Yes
Alex Peters 3 Yes
Alex Peters 11 Yes
Alex Peters 5 Yes
Alex Peters 7 No
Alex Peters 7 Yes
Alex Peters 9 Yes
Chris Berrisford 11 Yes
Chris Berrisford 9 No
Chris Berrisford 8 Yes
Chris Berrisford 10 Yes
Andy Webber 0 No
Andy Webber 12 Yes
Andy Webber 12 Yes
Andy Webber 11 Yes
Andy Webber 11 Yes
Andy Webber 9 No
Andy Webber 14 No
Andy Webber 15 No
Andy Webber 13 Yes
Andy Webber 12 Yes
Andy Webber 14 No
Andy Webber 11 No

Expected output:

Member Points Local Trophy
Alex Peters 15 Yes
Alex Peters 13 Yes
Alex Peters 11 Yes
Alex Peters 9 Yes
Alex Peters 9 Yes
Alex Peters 7 Yes
Andy Webber 13 Yes
Andy Webber 12 Yes
Andy Webber 12 Yes
Andy Webber 12 Yes
Andy Webber 11 Yes
Andy Webber 11 Yes
Chris Berrisford 11 Yes
Chris Berrisford 10 Yes
Chris Berrisford 8 Yes

Actual output

Member Points Local Trophy
Alex Peters 15 Yes
Alex Peters 13 Yes
Alex Peters 11 Yes
Alex Peters 9 Yes
Alex Peters 9 Yes
Andy Webber 13 Yes
Andy Webber 12 Yes
Andy Webber 12 Yes
Andy Webber 12 Yes
Chris Berrisford 11 Yes
Chris Berrisford 10 Yes