Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Question Unanswered: Need help retrieving rows based on MAX( value ) in FK table

    Hi everyone,

    First let me say thank you in advance - nice to have a place to come when you start banging your head on the wall. Mental block, consider the following two tables:

    TABLE A:
    id int(32) auto_increment
    varchar(2) value

    sample data:
    1, a
    2, b
    3, c


    TABLE B:
    id int(32) auto_increment
    stamp DATETIME
    a_id int(32) // this is a foreign key to A.id

    sample data:
    1, 2005-12-12 00:00:00, 1
    2, 2005-12-14 00:00:00, 1
    3, 2005-12-15 00:00:00, 1
    4, 2005-11-12 00:00:00, 2
    5, 2005-10-15 00:00:00, 2
    6, 2004-11-05 00:00:00, 3


    In essence, tables A and B share a 1 to N relationship. One A-table element, can have many B-table elements referencing it through the a_id column value.

    The result set I need, must contain the values from A and B table, but only for the latest entry in the B table (latest entry meaning the maximal stamp value). For example, the query would yield:

    A.id, A.value, B.id, B.stamp
    1, a, 3, 2005-12-15 00:00:00
    2, b, 4, 2005-11-12 00:00:00
    3, c, 6, 2004-11-05 00:00:00

    Any help you can give is appreciated! I'm completely lost on how to join the max result with the original table.

    Thanks!!
    Atari
    Last edited by Atari; 09-22-05 at 02:41. Reason: better title

  2. #2
    Join Date
    Jun 2005
    Posts
    23
    Code:
    SELECT
     a.id,
     a.value,
     b.id,
     b.stamp
    FROM
     B AS b
    INNER JOIN
     A AS a
    ON
     b.a_id=a.id
    WHERE
     b.stamp=(
                    SELECT 
                         MAX(b2.stamp)
                         FROM B AS b2
                         WHERE
                         b.a_id=b2.a_id
                 )

Posting Permissions

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