Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group