On Fri, Apr 15, 2005 at 08:22:31 -0500,
Lance Massey <lmspam(at)neuropop(dot)com> wrote:
> In mySQL I could select the most recent addresses with
> "Select *, max(ID_extended) from customers group by ID"
> In postgreSQL that apparently doesn't work.
If you you can live with a Postgres specific solution you can use
the DISTINCT ON clause to do this.
SELECT DISTINCT ON (id) * FROM customers ORDER BY id, id_extended DESC;
There has been some talk making the changes needed to detect that the
grouping is on the primary key, but I dn't know if that will make it in 8.1.
Until then, the standard conforming solution looks like this.
FROM customers AS a,
(SELECT id, max(id_extended) FROM customers GROUP BY id) AS b
WHERE a.id = b.id;
Note I didn't run this query, so there could be typos.
In response to
pgsql-novice by date
|Next:||From: operationsengineer1||Date: 2005-04-16 11:22:01|
|Subject: Database Encoding|
|Previous:||From: Cima||Date: 2005-04-15 22:42:40|
|Subject: oids as primary keys?|