Most recent from list using foreign key help.

Saeven

Hello Everyone..

Given two tables.

Table A

id int( 8 );
b_id int( 8 );
date timestamp(14);
PRIMARY KEY ( id )

Table B

id int( 8 );
val char( 1 );
PRIMARY KEY ( id )

And the following sample data

Table A

1, 5, 20030104223003
2, 5, 20030204113432
3, 5, 20040101010101

Table B

5, T

The question is, in one query, how can we retrieve, the joined set of both tables on A.b_id = B.id, only for the entry for which the date in A is maximal.

Example return would be:

A.id, A.b_id, A.date, B.val:
3, 5, 20040101010101, T

Thanks in advance!!

Roger_Ramjet

You use the group by clause and aggregate function max

SELECT a.id, a.b_id, MAX(a.date), b.val
FROM a,b
WHERE a.b_id=b.id
GROUP BY a.b_id

LordShryku

MySQL may accept that, but any normal SQL parsing engine will choke if you don't group on all of the selected non-aggregate elements.

Roger_Ramjet

Quite true. I only typed the thing quickly as an indication and without much thought. Hopefully Saeven wil look up the correct syntax for his DBMS. I suppose I should be more carefull with my examples.