SQL question to eliminate duplicate names 
Author Message
 SQL question to eliminate duplicate names

I have a table of times achieved by athletes at various competitions.
If I want the 10 fastest times for a given category of athletes,
how do I eliminate duplicates where the athlete competed at more
than one competition and placed in the top 10 more than once.
In other words, I want not just one occurrence per name, but the
fastest occurence per name.



Sun, 04 Jul 1999 03:00:00 GMT  
 SQL question to eliminate duplicate names

SELECT Distinct Name TOP 10 from TimeTable

--
David W. Forest
LAN Specialist
Information Technologies
Lear Corporation - Ford Division
SBN Level 2

If you're not scared, you don't understand.



Quote:
> I have a table of times achieved by athletes at various competitions.
> If I want the 10 fastest times for a given category of athletes,
> how do I eliminate duplicates where the athlete competed at more
> than one competition and placed in the top 10 more than once.
> In other words, I want not just one occurrence per name, but the
> fastest occurence per name.



Mon, 05 Jul 1999 03:00:00 GMT  
 SQL question to eliminate duplicate names

Hello Jim,

Do a totals query grouping on the Athlete's Name field and choose "Min" for
the total under the Time field:

SELECT times.name, Min(times.time) As BestTime FROM times GROUP BY
times.name;

  That will give you a list of each athlete and lowest(best) time.  You can
then do a top-ten query on that query.

I hope this helps,

Larry Tubbs, MCPS, MCSD
Silverleaf Vacation Club

http://www.flash.net/~enigma/



Quote:
> I have a table of times achieved by athletes at various competitions.
> If I want the 10 fastest times for a given category of athletes,
> how do I eliminate duplicates where the athlete competed at more
> than one competition and placed in the top 10 more than once.
> In other words, I want not just one occurrence per name, but the
> fastest occurence per name.



Mon, 05 Jul 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Eliminating duplicate memo fields must be done !!!

2. Eliminating duplicate records with abbreviated addresses

3. eliminate duplicates in array?

4. Eliminating Duplicates from Report

5. Eliminating Duplicate Values from Combo Box

6. eliminating duplicates in a database table

7. ADO SQL Recordsets Return Duplicate Names

8. Duplicate field name difference with SQL and Access

9. Eliminating multiple logins in VB6 with SQL svr

10. Eliminating unwanted whitespace - Listbox question

11. Eliminating unwanted whitespace - Listbox question

12. Removing duplicates names but not losing their other fields

 

 
Powered by phpBB® Forum Software