Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: Retrieving the MAX value of multiple elements in a table.

    Hi,

    I have a table similar to the one below.

    +----+---------------------+-------------+
    | id | date | measurement |
    +----+---------------------+-------------+
    | 1 | 2004-06-15 00:00:00 | 113452 |
    | 3 | 2004-06-15 00:00:00 | 4855 |
    | 4 | 2004-06-15 00:00:00 | 13285 |
    | 1 | 2004-06-16 00:00:00 | 13953 |
    | 2 | 2004-06-16 00:00:00 | 113 |
    | 3 | 2004-06-16 00:00:00 | 9495 |
    | 4 | 2004-06-16 00:00:00 | 22 |
    | 2 | 2004-06-17 00:00:00 | 3475 |
    | 3 | 2004-06-17 00:00:00 | 84857 |
    | 4 | 2004-06-17 00:00:00 | 485858 |
    | 1 | 2004-06-18 00:00:00 | 95986 |
    | 4 | 2004-06-18 00:00:00 | 555643 |
    | 1 | 2004-06-19 00:00:00 | 432 |
    | 2 | 2004-06-19 00:00:00 | 843 |
    | 3 | 2004-06-19 00:00:00 | 47835 |
    +----+---------------------+-------------+

    How do I obtain a list of last date (i.e. the latest entry) for each of the ids and the corresponding measurement values with a SQL query to produce a table like the following?

    +----+---------------------+-------------+
    | id | date | measurement |
    +----+---------------------+-------------+
    | 1 | 2004-06-19 00:00:00 | 432 |
    | 2 | 2004-06-17 00:00:00 | 3475 |
    | 3 | 2004-06-19 00:00:00 | 47835 |
    | 4 | 2004-06-18 00:00:00 | 555643 |
    +----+---------------------+-------------+

    Thank you in advance,
    Amish

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.id
         , t1.date
         , t1.measurement 
      from yourtable as t1     
    inner
      join yourtable as t2
        on t1.id
         = t2.id
    group
        by t1.id
         , t1.date
         , t1.measurement 
    having t1.date
         = max(t2.date)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    4
    Hi,

    I tried the query you suggested but mysql returns the following "Empty set (0.00 sec)".

    FYI: I am using version 4.0.15a on Slackware 9.1 (with kernel 2.4.26).

    Thanks.
    Amish

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you said your table is "similar" to the one shown

    can you show the exact query you ran?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    4
    Hi,

    I tested your suggested query on the table listed above. See below for a copy and paste from the mysql client.

    The actual table has 100s of lines (that why I haven't posted it) but it has a similar structure to the example.
    I haven't tried your query on it. To currently get the result I am looking for from the real table I am running a "select id,max(date) from mytable group by id;" and then a "select id,measurement from mytable where id=X and date=Y;" for each id X and date Y as received from the first query.

    ################################################
    mysql> select * from mytable;
    +----+---------------------+-------------+
    | id | date | measurement |
    +----+---------------------+-------------+
    | 1 | 2004-06-15 00:00:00 | 113452 |
    | 3 | 2004-06-15 00:00:00 | 4855 |
    | 4 | 2004-06-15 00:00:00 | 13285 |
    | 1 | 2004-06-16 00:00:00 | 13953 |
    | 2 | 2004-06-16 00:00:00 | 113 |
    | 3 | 2004-06-16 00:00:00 | 9495 |
    | 4 | 2004-06-16 00:00:00 | 22 |
    | 2 | 2004-06-17 00:00:00 | 3475 |
    | 3 | 2004-06-17 00:00:00 | 84857 |
    | 4 | 2004-06-17 00:00:00 | 485858 |
    | 1 | 2004-06-18 00:00:00 | 95986 |
    | 4 | 2004-06-18 00:00:00 | 555643 |
    | 1 | 2004-06-19 00:00:00 | 432 |
    | 3 | 2004-06-19 00:00:00 | 47835 |
    +----+---------------------+-------------+
    14 rows in set (0.00 sec)

    mysql> select t1.id, t1.date, t1.measurement from mytable as t1 inner join mytable as t2 on t1.id = t2.id group by t1.id , t1.date, t1.measurement having t1.date=max(t2.date);
    Empty set (0.00 sec)
    ################################################

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i'm sorry, i do not know what you are doing wrong

    perhaps it is a bad datatype for one of your columns?

    but i know my query works

    watch me create your data --
    Code:
    create table mytable
    ( id tinyint 
    , `date` date
    , measurement integer
    );
    insert into mytable (id,`date`,measurement) values
      ( 1, '2004-06-15', 113452 )
    , ( 3, '2004-06-15', 4855 )
    , ( 4, '2004-06-15', 13285 )
    , ( 1, '2004-06-16', 13953 )
    , ( 2, '2004-06-16', 113 )
    , ( 3, '2004-06-16', 9495 )
    , ( 4, '2004-06-16', 22 )
    , ( 2, '2004-06-17', 3475 )
    , ( 3, '2004-06-17', 84857 )
    , ( 4, '2004-06-17', 485858 )
    , ( 1, '2004-06-18', 95986 )
    , ( 4, '2004-06-18', 555643 )
    , ( 1, '2004-06-19', 432 )
    , ( 3, '2004-06-19', 47835 )
    ;
    select * from mytable
    ;
    
    id     date    measurement
     1  2004-06-15  113452
     3  2004-06-15    4855
     4  2004-06-15   13285
     1  2004-06-16   13953
     2  2004-06-16     113
     3  2004-06-16    9495
     4  2004-06-16      22
     2  2004-06-17    3475
     3  2004-06-17   84857
     4  2004-06-17  485858
     1  2004-06-18   95986
     4  2004-06-18  555643
     1  2004-06-19     432
     3  2004-06-19   47835
    now watch me run the query and let's see what we get --
    Code:
    select t1.id
         , t1.date
         , t1.measurement 
      from mytable as t1 
    inner 
      join mytable as t2 
        on t1.id = t2.id 
    group 
        by t1.id
         , t1.date
         , t1.measurement 
    having t1.date=max(t2.date)
    
    id     date    measurement
     1  2004-06-19     432
     2  2004-06-17    3475
     3  2004-06-19   47835
     4  2004-06-18  555643
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Posts
    4
    Interesting! (or is that a bug in mysql 4.0.15a - I'll try 4.0.20 and see if the problem persists).

    I created the table and ran the query exactly as you did and it worked.
    I then dropped the table and recreated with date as a "timestamp" date type and query failed!
    I stopped mysqld and restarted it without the --new command line option and the query worked with date created as "timestamp".

    I hope this problem does not persist with version 4.1 when the new timestamp format is used.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •