Duplicate runs 2

[crewchiefpro6]

I need to display the top 32 time runs even though all cars may have multiple runs all with separate times. I need to filter out the top 32 qualifiers (cars) and then display their fastest time. Currently the program is giving me all of the top 32 qualifiers (cars) times. I only need their fastest run.


Thanks Don

 

[TheRambler]

Try something like this:

SELECT TOP 32 car, MIN(run) as bestTime ;
FROM yourTable ;
GROUP by car ;
ORDER by bestTime

 

[crewchiefpro6]

This is a good partial answer. The problem is that one person may have three of the best times. I need each racer (car) only one time. When the car has been put in the data it can not be in the data again. Also the best time of that (car) must be the one is the data.


Thanks

 

[Mike Lewis]

Don,

The way you've described the problem, it seems to me that TheRambler's solution is the right one.

Let me put it this way:

You want to find each car's best time. Then find the top 32 of those best times and their corresponding cars. Is that right? If so, TheRambler's answer will give you that (I think).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[KoenPiller]

Chief,

If you are not satisfied with Rambler's solution I believe you should describe the problem a little bit different and more consistent, once you are talking about qualifiers (cars) next about persons and also about racer (car)
what are you looking for: (i) the 32 best drivers in any car, (ii) 32 fastest car regardless who is the driver, or (iii) the 32 best drivers in a specific car?
Koen

 

[crewchiefpro6]

I appoligize for the lack of clarity. Currently the program is reporting the top 32 best race car times. in those times a car could be listed more than one time. (each car gets 2-3 passes to get into the to 32.) What I need to do is ensure that if a car is listed more than one time tha only that car's best time is listed and them the car is not listed again. The program does currently take the best 32 time and rank them, but if Mr Jones has two of the best times he is currently listed two times. I need to take Mr Jones best time and rank that and not include his second best time. Each race car can only be listed one time and then ranked against the other cars in order of best time. Example below


Thank You


Don





Car # Time driver
1 11 sec Mr Jones
2 12 sec Mr Smith
3 12.3 sec Bob
4 13 sec Duplicate of Mr Jones
5 13.5 sec John
6 13.7 sec Dan
7 15 sec Dave
8 16 sec Jerry
9 16.5sec Pat




I need the duplicate of Mr Jones to not be in the data. Take that run out and put car with the best time with not duplicates of cars. Currently I can not figure out how to take the duplicate cars out.

 

[Mike Lewis]

Don,

Sorry to labout the point, but I've just run TheRambler's code on your data (but changing TOP 32 to TOP 8), and it seems to give exactly the result you want:

Jones 11.00
Smith 12.00
Bob 12.30
John 13.50
Dan 13.70
Dave 15.00
Jerry 16.00
Pat 16.50

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[KoenPiller]

Don,

I am sorry I also overlooked, Ramblers' solution is perfect.

Koen

 

[crewchiefpro6]

Thank you. My wife posted the message for me while I was in training with Marcia and Andy, and I could not access the tek-tips forum.

When I called her to get the answer she forgot to tell me a line of code that had the Group By clause. When I added this line it worked fine.

Don

 

[TheRambler]

My head was spinning!
Thanks for the stars.