At 16:07 -0800 1/27/02, [EMAIL PROTECTED] wrote: >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.
1) Why do you think that query should return that result? 2) Why are you selecting the id value? In other words, remove id from the query and see what happens. > >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
