I think that the command "SELECT id, ref, MAX(timestamp), field FROM t GROUP BY ref;" should give me fields with the latest timestamp for each ref in my table. But it doesn't.
I have the following table: mysql> describe t; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | ref | int(10) unsigned | | | 0 | | | timestamp | varchar(16) | | | | | | field | varchar(16) | | | | | +-----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> select * from t; +----+-----+------------------+-----------------+ | id | ref | timestamp | field | +----+-----+------------------+-----------------+ | 1 | 1 | 2001112711:51:17 | 1 version one | | 2 | 1 | 2002010713:35:26 | 1 version two | | 3 | 1 | 2002010812:29:22 | 1 version three | | 4 | 1 | 2002010814:59:34 | 1 version four | | 5 | 2 | 2001112711:51:17 | 2 version one | | 6 | 2 | 2002010713:35:26 | 2 version two | | 7 | 2 | 2002010812:29:22 | 2 version three | | 8 | 2 | 2002010814:59:34 | 2 version four | +----+-----+------------------+-----------------+ 8 rows in set (0.00 sec) I would like to find the newest version of FIELD associated with each REF. That is, I would like to print out the following table: mysql> select INSERT MAGIC HERE; +----+-----+------------------+-----------------+ | id | ref | timestamp | field | +----+-----+------------------+-----------------+ | 4 | 1 | 2002010814:59:34 | 1 version four | | 8 | 2 | 2002010814:59:34 | 2 version four | +----+-----+------------------+-----------------+ 8 rows in set (0.00 sec) I think the following command should work, but it does not: mysql> SELECT id, ref, MAX(timestamp), field FROM t GROUP BY ref; +----+-----+------------------+---------------+ | id | ref | max(timestamp) | field | +----+-----+------------------+---------------+ | 1 | 1 | 2002010814:59:34 | 1 version one | | 5 | 2 | 2002010814:59:34 | 2 version one | +----+-----+------------------+---------------+ 2 rows in set (0.00 sec) As you can see, the GROUP BY command is grabbing the first set of fields it finds, which don't match the max timestamp. What am I doing wrong? Thanks, Dave --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
