--- In firebird-support@yahoogroups.com, "petr.jakes" wrote:

> I know, very common question here but...
>
> Just to simplify the problem, two simple table:
>
> Person:
> ID, NAME
>
> Message:
> ID, PERSON_ID, SUBJECT, TIMESTAMP
>
> I would like to have SELECT, which returns:
>
> Person.NAME, Message.SUBJECT, Message.TIMESTAMP
>
> where the rows included in the SELECT results are the rows with the youngest TIMESTAMP for each NAME only. SELECT MAX does not work, because Message.SUBJECT values in the table are unique.
>
> Thanks for your comments/suggestions.

Hi Petr,
assuming you want the latest record for each PERSON_ID rather than NAME (there may be two persons with the same name), I'd say you're simply looking for something like

SELECT P.NAME, M.SUBJECT, M.TIMESTAMP
FROM PERSON p
JOIN MESSAGE m ON p.ID = m.PERSON_ID
WHERE NOT EXISTS
(SELECT *
FROM MESSAGE m2
WHERE m2.PERSON_ID = m.PERSON_ID
AND m2.TIMESTAMP > m.TIMESTAMP)

Alternatively, you can use this (same select as above, just written a bit differently):

SELECT P.NAME, M.SUBJECT, M.TIMESTAMP
FROM PERSON p
JOIN MESSAGE m ON p.ID = m.PERSON_ID
LEFT JOIN MESSAGE m2 ON p.ID = m2.PERSON_ID
AND m.TIMESTAMP < m2.TIMESTAMP
WHERE m2.ID IS NULL

HTH,
Set