# GROUP and MIN problem....

## Recommended Posts

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?

##### Share on other sites

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

##### Share on other sites

```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
```

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

• 3 weeks later...

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...

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.