Thread: query syntax question

I'm migrating from mySQL where my database tables have an "ID" column
and a an "ID_extended" column. This was done so I could keep a history
e.g. a customer gets an ID when signing on then if they move a new row
is inserted, keeping the old ID, but incrementing the extended ID.

I'm sure there's a better way, but I digress.

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 if do:
"Select ID, max(ID_extended) from customers group by ID"

I get a list of the the correct IDs and extended IDs - however, I can't
seem to structure a query where I also retrieve address info.

Is there a way to use the results of that query as inputs to another?
Or is there a better way to write the previous query? Or should I
re-think the data structure since postgreSQL has objects and arrays
available?...

thanks,

L

On Fri, Apr 15, 2005 at 08:22:31 -0500,
  Lance Massey <lmspam@neuropop.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.