Hey,
I have a grouped query that is returning multiple records per grouping.
I have read up on and tried the proposed solutions that others have posted... to no avail.
I am trying to have the query return only the highest 10 records per group.
For simplicity sake, the groups are peoples names, and the 10 highest records are going to be each persons best finish result in a race.
The sorting and grouping is complete...
If anyone out there thinks they can help me straighten this one out... id be grateful!
![Cheers :cheers: :cheers:](/styles/default/custom/smilies/cheers.gif)
Post or PM if you need any additional info.
I have posted the sql view for the query below.
This is from a backed up version of the DB, so table names are a mess... this much I am already aware of. Once I solve this one, the whole DB is getting an clean up and re-write.
The RiderPlate, RiderFirst, RiderLast, RiderSex, AgeCategory, and RiderCategory are what creates the unique group.
What I need to find is the highest 10 values within the "PointsEarned" field.
Thanks in advance!!!
AJ
______________________________________
SELECT [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned]
FROM [1 Riders and finishes] INNER JOIN Riders ON [1 Riders and finishes].RiderPlate = Riders.RiderPlate
GROUP BY [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned];
I have a grouped query that is returning multiple records per grouping.
I have read up on and tried the proposed solutions that others have posted... to no avail.
I am trying to have the query return only the highest 10 records per group.
For simplicity sake, the groups are peoples names, and the 10 highest records are going to be each persons best finish result in a race.
The sorting and grouping is complete...
If anyone out there thinks they can help me straighten this one out... id be grateful!
![Cheers :cheers: :cheers:](/styles/default/custom/smilies/cheers.gif)
Post or PM if you need any additional info.
I have posted the sql view for the query below.
This is from a backed up version of the DB, so table names are a mess... this much I am already aware of. Once I solve this one, the whole DB is getting an clean up and re-write.
The RiderPlate, RiderFirst, RiderLast, RiderSex, AgeCategory, and RiderCategory are what creates the unique group.
What I need to find is the highest 10 values within the "PointsEarned" field.
Thanks in advance!!!
AJ
______________________________________
SELECT [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned]
FROM [1 Riders and finishes] INNER JOIN Riders ON [1 Riders and finishes].RiderPlate = Riders.RiderPlate
GROUP BY [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned];