Need help with MAX and subquery

Atari · #1 (**permalink**) 09-22-05, 01:27

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

tombell · #2 (**permalink**) 09-22-05, 06:26

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
             )

Internet Services

Web Development

Digital Marketing

Consumer Tech