Using DISTINCT

[J-F]

This is for a website which holds triathlon results for standard distance triathlons throughout the year. The following gives me a list of results from the year for all competitors. The trouble is that if a competitor enters more than once, they will be shown with more than one result.

What I want to do is only show their best result. I have experimented with DISTINCT but can't come up with a solution. Can anyone help please. The Primary key I want to keep DISTINCT is both Competitors.SB and Results.SB which are joined from 2 tables (Competitors and Results). There is a 3rd table called clubs.

SELECT Competitors.SB, Competitors.Sex, Competitors.FirstName, Competitors.LastName, Results.TotalTime, Results.EventDate, Results.Category, Results.ID As ResultsID, clubs.ID As ClubID, clubs.name
FROM clubs INNER JOIN (Competitors INNER JOIN Results ON Competitors.SB = Results.SB) ON clubs.ID = Results.ClubID
ORDER BY Results.TotalTime;

 

[Rich]

Look up the TopValue property

 

[J-F]

The trouble is that the DB comprises of hundreds of competitors, some of which have taken part on more than one occassion. I just want to show their best times without deleting their other times, so I can't see how TopValue would help.

 

[Rich]

You don't have to delete anything, the Top property will select the highest score from a list, there have been many examples posted here, the search facility here will reveal them, the Max function in a totals query will do the same in this case, assuming that the race position is decided by the total number of points, if you're recording actual times it's more difficult.