GROUP and MIN problem....

[ViperSBT]

Table: speed

field1: id

field2: date

field3: time

field4: competitor

 

Now using the following example for the Speed Table:

Field1

Field2

Field3

Field4

104/04/0612.0001

205/04/0611.0001

308/04/0612.5001

410/04/0610.0001

 

I want to know the fastest time and the date that the competitor ran and have a rather extensive query that this is a subset out of.  But what is happening is that when I use the MIN() and GROUP BY I get the correct 'time' but I don't get the correct date.  It returns the '10.00' as expected but for the date I am getting '04/04/06' which is just the first entry in that series of the GROUP...  How do I get the appropriate date?

[Barand]

SELECT a.id, a.date, a.time FROM speed a

INNER JOIN (SELECT id, MIN(time) as fastest FROM speed GROUP BY id) as b

ON a.id = b.id AND a.time = b.fastest

[bubblegum.anarchy]

SELECT speed.* FROM speed
     INNER JOIN ( SELECT competitor, min(speed.time) AS time FROM speed GROUP BY competitor ) AS fastest
          ON speed.competitor = fastest.competitor AND speed.time = fastest.time
ORDER BY speed.time

[Barand]

You're right. Where I had "id", I should've put "competitor"

[ViperSBT]

OK, maybe I am doing this INNER JOIN wrong, but here is the query I put together:

SELECT d.cname, c.cname AS club, c.cnumber, speed.event, speed.time, d.cname, CONCAT(h.fname, ' ', h.lname) AS owner, d.height, date_format(t.date, '%m-%d-%y') as date
FROM dogs d 
INNER JOIN (SELECT s.dog, s.event, MIN(s.time) AS time FROM singles s GROUP BY s.dog) AS speed ON d.dnumber = speed.dog
JOIN clubs c ON d.club = c.cnumber
JOIN handler h ON d.owner = h.hnumber
JOIN tournament t ON speed.event = t.enumber
WHERE d.breed = 45 
ORDER BY speed.time

All of the results are as desired, but I am still not getting the right 'speed.event' to lookup the tournament number to get the date for the tournament...  The 'speed.time' is correct as I am getting the MIN(s.time) for the desired competitor, but the 's.event' that is being returned is the first that it finds for the competitor...

 

As an example, here is the actual table information for one of the competitors:

ID  dog  time  event

67  15  3.768  4

233  15  3.835  32

1086  15  3.693  73

 

And this is what the Query returns for that competitor:

Sage  Touch N Go  2  4  3.693  Sage  Stephanie Franks  12  06-12-05

 

So, I am getting the MIN(time) of 3.693 but it thinks that was done on 06-12-05 which was event 4, not 73... Arrgghhhh

[bubblegum.anarchy]

What is the purpose of s.event in the subquery SELECT?

[ViperSBT]

s.event refers to t.enumber which can be used to look up specific information about the event that the time was earned, in this case I am wanting the date of the event.

[bubblegum.anarchy]

I am not so sure s.event is the value you are expecting... run the subquery alone to verify the value is what you expect.

[ViperSBT]

I don't understand what you are saying...  Not being well versed on using subselects I am not sure why it is needed there...  My problem is still that the results I get from the GROUPING and the MIN() do not match line to line...

[bubblegum.anarchy]

I mean, look at the results of the following query:

 

SELECT s.dog, s.event, MIN(s.time) AS time FROM singles s GROUP BY s.dog

 

s.event is not necessarily going to be what you are expecting, only s.dog is going to match s.time, s.event on the other hand will just be the first record of the entire group of s.dog and not necessarily match s.dog and s.event.

[ViperSBT]

That is exactly what I am saying my problem is, how do I get the appropriate s.event?

[bubblegum.anarchy]

By joining the singles table to a result set of just dog and time using the dog and time values.

[Barand]

Which takes it full circle back to bubblegum's working solution back in reply #2