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

Re: query syntax question

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Lance Massey <lmspam(at)neuropop(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: query syntax question
Date: 2005-04-15 23:19:01
Message-ID: 20050415231901.GB12716@wolff.to (view raw or flat)
Thread:
Lists: pgsql-novice
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.
SELECT a.*
  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: operationsengineer1Date: 2005-04-16 11:22:01
Subject: Database Encoding
Previous:From: CimaDate: 2005-04-15 22:42:40
Subject: oids as primary keys?

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