MySQL 4. Problem with min() and GROUP BY 1

[reimers]

Hi,

I wish to select the value of IDrecord with the oldest date from the table contenttracking. However when using min() and GROUP BY it seems to be mixing different entries in the table.

Table:

Contenttracking
=======================================
ID IDcontent IDrecord time
---------------------------------------
1 329 17 2005-05-29 13:46:49
2 329 24 2005-05-29 20:33:57
=======================================


Query:

SELECT IDcontent, IDrecord, min( time ) AS time
FROM contenttracking
GROUP BY IDcontent


Result host (MySQL 4.?.?):

=======================================
IDcontent IDrecord time
---------------------------------------
329 24 2005-05-29 13:46:49
=======================================

Result on my own testserver (MySQL 4.0.23):

=======================================
IDcontent IDrecord time
---------------------------------------
329 17 2005-05-29 13:46:49
=======================================

In the result on my web host the correct date is selected however the result is merged with a different entry of IDrecord. How do I make sure it returns the value of IDrecord related to the correct time? Or is this simply a bug in a version of MySQL 4?

Regards,

Reimers

 

[reimers]

Further search seems to indicate, that the random selection is simply a feature in MySQL.

 

[TonyGroves]

If you just want the one record with the oldest time, you could simply use:[tt]
SELECT *
FROM contenttracking
ORDER BY time
LIMIT 1[tt]

What you were getting was the minimum time for each idcontent value, as well as one (which one is undefined) of the values of idrecord for each incontent value.

 

[TonyGroves]

Sorry, you can ignore the [tt] at the end of the query terxt.

 

[r937]

it's not a mysql bug, it's a "feature"

in any other database, your query would give a syntax error

only in mysql can you have a column in the SELECT that isn't in the GROUP BY

however, please note, mysql does warn you that if the "hidden" column is not unique within each group, the results are unpredictable

see 12.9.3. GROUP BY with Hidden Fields

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)