List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 28 2002 12:56am
Subject:Re: Why does "MAX(t.a), t.b" return fields from different records?
View as plain text  
At 16:07 -0800 1/27/02, dave-mlist@stripped 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

Thread
Why does DISTINCT take so long time ??Fournier Jocelyn [Presence-PC]18 Jan
  • Re: Why does DISTINCT take so long time ??Sinisa Milivojevic18 Jan
  • Re: Why does DISTINCT take so long time ??Fournier Jocelyn [Presence-PC]18 Jan
    • Re: Why does DISTINCT take so long time ??Sinisa Milivojevic18 Jan
      • Re: Why does DISTINCT take so long time ??Michael Widenius26 Jan
        • Why does "MAX(t.a), t.b" return fields from different records?dave-mlist28 Jan
          • Re: Why does "MAX(t.a), t.b" return fields from different records?Paul DuBois28 Jan
            • Re: Why does "MAX(t.a), t.b" return fields from different records?dave-mlist28 Jan
  • Re: Why does DISTINCT take so long time ??Fournier Jocelyn [Presence-PC]18 Jan
  • Re: Why does DISTINCT take so long time ??Anvar Hussain K.M.19 Jan
    • Re: Why does DISTINCT take so long time ??Kalok Lo19 Jan
      • Re: Why does DISTINCT take so long time ??Anvar Hussain K.M.21 Jan
    • Re: Why does DISTINCT take so long time ??Jocelyn Fournier21 Jan