Frank Bax <fbax@sympatico.ca> writes:
> It seems to me the underlying question here comes up often. Might I
> suggest that we add a new question to section 4 of FAQ:
> Q: How can I retrieve other fields in same row as result returned by an
> aggregate function such as max()?
> A: Is my "SQL1" the best/only answer to this question?
DISTINCT ON is also a commonly suggested answer, viz
SELECT DISTINCT ON (f1) * FROM data_a ORDER BY f1, timestamp DESC;
One point about your SQL1 is that if there are multiple rows sharing
the max timestamp, the join will return all of them. This might or
might not be what's wanted. The DISTINCT ON method gives the other
answer: you get just one row per f1 value. As given it'd be a random
one of the max-timestamp rows, but you could add additional ORDER BY
columns to prioritize them.
regards, tom lane
