Removing NEAR duplicate results

  • Thread starter Thread starter sharpie23
  • Start date Start date
S

sharpie23

I have a database of runners times on each course. I also have the
year they ran that time and their age that year. I am trying to make a
top 10 list for each age and a top 10 list for all runners on a
particular course.
The problem I am running into is that a runner may have had multiple
times on a certain course that are on the top 10 list. I only want
that particular runners' top time

For intance a top 10 result now may read

Matt 2006 16:56
Joe 2007 7:00
Matt 2005 17:11
Matt 2006 17:16
Matt 2007 17:24
Joe 2005 17:25
Fred 2006 17:34

etc...

I want it ot just return
Matt 2006 16:56
Joe 2007 17:00
Fred 2006 17:34

and then fill in the rest of the top 10


Obviously the only portion that is a duplicate is the name and course.
The year may or may not be different. The times will be different. The
age may or may not be different.


any ideas?


-Ryan Sharp
 
Hi -

Your example doesn't show age. How are you computing it? If based on the
year ran, that could be a problem, i.e. today is 30 Sep, the runner's age
will differ by one year based on whether their birth date (mm/dd) is less
than or >= today's date (mm/dd). Think this would need to be resolved before
going on with further calculations.

Bob
 
Hi -

Your example doesn't show age. How are you computing it? If based on the
year ran, that could be a problem, i.e. today is 30 Sep, the runner's age
will differ by one year based on whether their birth date (mm/dd) is less
than or >= today's date (mm/dd). Think this would need to be resolved before
going on with further calculations.

Bob
Click to expand...

I simly put their grade in school. 9, 10, 11, or 12
 
Ryan:

Ignoring the question of age computation for the moment so as to illustrate
the principle involved in returning the top 10 runners' times per course a
query would go something like this:

SELECT TOP 10 *
FROM (SELECT Course, RunnerID, Runner,
MIN(RunTime) AS BestTime
FROM YourTable
GROUP BY Course, Runner)
ORDER BY BestTime;

To include the RaceYear column in the results join the above query to the
original table ON YourQuery.Course = YourTable.Course AND YourQuery.RunnerID
= YourTable.RunnerID AND YourQuery.BestTime = YourTable.RunTime and include
the RaceYear column in the final query's SELECT clause. Should a runner have
run exactly the same time best times in two races at the same course the
final query would of course return two rows for that runner.

Note that I have not used Year or Time as column names in this example.
These are the names of built in functions so should be avoided as column
names.

Also using the runner's name as a unique identifier would be unreliable as
two runner's could have the same name. This is why RunnerID is included;
this would be a unique numeric column identifying each runner independently
of their name.

While for simplicity I've assumed a single table above there should really
be separate Runners and Races tables of course, along with a third table
which models the many-to-many relationship between them, all three of which
would be joined in the FROM clause of the subquery in the above example.

As regards the question of age I can only assume, as you appear to be
identifying when each race was run solely by the year, that you are regarding
a runner's age as that at the start of the year or similar, rather than their
true age when each race was run. If so, to return the top 10 per age a
similar query would be used, but with the subquery grouped by Age instead of
Course. If not, and you want to group by the runner's age at the date each
race was run then you'd need to compute this on the basis of the runner's
date of birth column (in the Runners table) and a race date column (in the
Races table). You'll find a couple of means of computing age at:


http://www.mvps.org/access/datetime/date0001.htm


Ken Sheridan
Stafford, England
 
Ryan:

Correction to my first reply. RunnerID would also need to be included in
GROUP BY clause:

SELECT TOP 10 *
FROM (SELECT Course, RunnerID, Runner,
MIN(RunTime) AS BestTime
FROM YourTable
GROUP BY Course, RunnerID, Runner)
ORDER BY BestTime;

As you are defining age by grade simply substitute grade for course in the
above to get the top 10 per grade:

BY clause:

SELECT TOP 10 *
FROM (SELECT Grade, RunnerID, Runner,
MIN(RunTime) AS BestTime
FROM YourTable
GROUP BY Grade, RunnerID, Runner)
ORDER BY BestTime;

When joining this to the table (or to a query joining the tables if the data
is in related tables as should really be the case) to get the years join ON
YourQuery.Grade = YourTable.Grade AND YourQuery.RunnerID = YourTable.RunnerID
AND YourQuery.BestTime = YourTable.RunTime.

Ken Sheridan
Stafford, England
 
Ryan:

Correction to my first reply. RunnerID would also need to be included in
GROUP BY clause:

SELECT TOP 10 *
FROM (SELECT Course, RunnerID, Runner,
MIN(RunTime) AS BestTime
FROM YourTable
GROUP BY Course, RunnerID, Runner)
ORDER BY BestTime;

As you are defining age by grade simply substitute grade for course in the
above to get the top 10 per grade:

BY clause:

SELECT TOP 10 *
FROM (SELECT Grade, RunnerID, Runner,
MIN(RunTime) AS BestTime
FROM YourTable
GROUP BY Grade, RunnerID, Runner)
ORDER BY BestTime;

When joining this to the table (or to a query joining the tables if the data
is in related tables as should really be the case) to get the years join ON
YourQuery.Grade = YourTable.Grade AND YourQuery.RunnerID = YourTable.RunnerID
AND YourQuery.BestTime = YourTable.RunTime.

Ken Sheridan
Stafford, England











- Show quoted text -
Click to expand...


This is not working...It is putting kids names with times that are not
theirs. and I am still getting repeat names.


Any other suggestions
 
Here's a couple which I definitely know work and seem directly analogous to
your requirements. The first returns the top three highest scoring players
from a particular fixture entered as a parameter, using only the lowest
scoring round per player at that fixture:

SELECT TOP 3 * FROM
(SELECT Fixture, GolferName, MIN(Score) AS BestScore
FROM Scores
WHERE Fixture = [Enter Fixture;]
GROUP BY Fixture,GolferName
ORDER BY MIN(Score));

The second does the same but for all fixtures.

SELECT Fixture, GolferName, MIN(Score) AS BestScore
FROM Scores AS S1
GROUP BY Fixture,GolferName
HAVING MIN(Score) IN
(SELECT TOP 3 MIN(Score)
FROM Scores AS S2
WHERE S2.Fixture = S1.Fixture
GROUP BY Fixture,GolferName
ORDER BY MIN(Score))
ORDER BY Fixture, MIN(Score);

In your case grade or course would be substituted for fixture.

Ken Sheridan
Stafford, England
 
Back
Top