Re: More of a SQL question, I guess.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Frank Bax <fbax(at)sympatico(dot)ca>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: More of a SQL question, I guess.
Date: 2007-08-14 15:11:04
Message-ID: 29093.1187104264@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Frank Bax <fbax(at)sympatico(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Steve Crawford 2007-08-14 18:23:13 Re: Postmaster start up problems (can't create lock file )
Previous Message Jasbinder Singh Bali 2007-08-14 14:49:59 Re: Postmaster start up problems (can't create lock file )