Select/Join weird problems......

Alfonso_C_

Hi to all:

I'm creation an small app to automatically create standings of the participants with their results of a competition.

I have two tables, one with the racers, and a second with their results. One racer can have several attempts and I only want one, their best result with the lowest time (think on a trial lap on a car race, racer one can have several attempts, but only want the one who had best results on a least time).

Well go for some DB stuff....

Table one is like this:
ID | Name | (Several columns describing participant)

Table two is like this:

Here is my select (beware, can be very ugly... I'm a spare time programmer 😃 )

$query = "SELECT ID,Name,RacerID,max(Laps) AS MaxLaps,min(Time) AS MinTme FROM Heats AS mangas LEFT JOIN Racers AS corredor IB mangas.RacerID = corredor.ID WHERE mangas.Class=\"E\" GROUP BY mangas.RacerID ORDER BY mangas.Vueltas DESC";

When I fire this query, the results are pretty weird, select the max value on the Laps column for one racer with their lowest time trial, but the data is on different rows, I need to select the row that has the highest value on Laps (Integer value) with the lowest value on Time (Float value - I need to retain fractions of second) not do a mix of their results... very puzzled. Any hints.

Thankyou in advance,
Alfonso

ultraslacker

The problem is MIN and MAX - you are doing this on one table. To see what's happening, try running the query after removing all references to racers table. You are returning rows, you know, so the only way to match the minimum time with the max laps is to have two instances of heat. Unless you are using a database other than mysql.

Something like this will work -

SELECT racers.id, max(laps.laps), min(time.time)
FROM racers
INNER JOIN heats AS laps
ON (racers.id = laps.id and laps.class = 'E')
INNER JOIN heats AS time
ON (racers.id = time.id and time.class = 'E')
GROUP BY racers.id

Alfonso_C_

Thankyou for the answer.

Didn't worked at all, maybe I'm messing or missing anything. I'll try to do but thanks for the light. 😕

tomhath

Suppose a racer had there three rows in Heats for the same HeatNo, which do you want? Highest Laps or lowset Time?

Laps Time
3-----4.5
4-----6
2-----1.5

ultraslacker

Maybe I misunderstood your requirements. You want the row per racer that has the highest laps and among any matching rows, grab the one with the lowest time? To get highest laps with the lowest time, use ORDER BY MaxLaps DESC, MinTime DESC in your original query. That won't always be the best race performance, by my definition...

If you want the best race performance, than just use a computation on time and laps in your original query, for example, ORDER BY laps / time DESC

Alfonso_C_

Originally posted by ultraslacker
Maybe I misunderstood your requirements. You want the row per racer that has the highest laps and among any matching rows, grab the one with the lowest time? To get highest laps with the lowest time, use ORDER BY MaxLaps DESC, MinTime DESC in your original query. That won't always be the best race performance, by my definition...

Think on a time trial. You have several opportunities of doing your best results and I need to choose the ones who have the best results in laps on the lowest time. Lets see:

Racer1 - try1 - 14 laps - 307.14 seconds.
Racer1 - try2 - 14 laps - 308.15 seconds.
Racer1 - try3 - 13 laps - 303.15 seconds.

In this case, you have 3 attempts, two with 14 laps and one with 13 laps, then, the two best ones are those with 14 laps and one has a slightly lower time 307.14 vs. 308.15, then the one who needs to choose is the one with 14 laps on 307.14 seconds.

Then... the query should have something like ORDER BY Laps DESC, Time DESC? or I'm wrong

TIA,
Alfonso Correas

Alfonso_C_

Originally posted by tomhath
Suppose a racer had there three rows in Heats for the same HeatNo, which do you want? Highest Laps or lowset Time?

Laps Time
3-----4.5
4-----6
2-----1.5

I need to chose the one with the highest laps on the lowest time... See my previous answer.

ultraslacker

Originally posted by Alfonso C.
Then... the query should have something like ORDER BY Laps DESC, Time DESC?

I think that's it - pretty easy and I went and made it too difficult. In fact, if you still need the OUTER JOIN to return unmatched rows in racers, your query just needs to be :

SELECT ID, name,laps, time
FROM racers
LEFT JOIN heats
ON (racers.id = laps.id and laps.class ='E')
GROUP BY racers.id
ORDER BY laps DESC, time desc;