Jump to content

GROUP and MIN problem....


ViperSBT

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?

Link to comment
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

Link to comment
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...

Link to comment
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.

Link to comment
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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.