From: Bruno Wolff III <bruno@wolff.to>
To: Jodi Kanter <jkanter@virginia.edu>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
Subject: Re: picking max from list
Date: Wed, 10 Dec 2003 14:53:40 -0600
Message-ID: <20031210205340.GA18726@wolff.to> (raw)
In-Reply-To: <3FD783F6.6030801@virginia.edu>
References: <3FD783F6.6030801@virginia.edu>
On Wed, Dec 10, 2003 at 15:37:10 -0500,
Jodi Kanter <jkanter@virginia.edu> wrote:
> I have a query that produces results similar to this:
>
> run# rd_pk group#
> 0 9209 5
> 1 9209 8
> 0 9520 2
> 1 9520 5
> 0 9520 etc....
> 0 8652
> 1 8652
> 2 8652
> 0 8895
> 1 8894
>
> Ultimately I want to know the group number for EACH rd_pk with the
> highest run number. Can this be done in one query? Or will I need to
> code with a loop?
> Thanks
You can do this using the postgres distinct on extension.
The query would look something like:
select distinct on (rd_pk) run, rd_pk, group from table_name
order by run desc;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, jkanter@virginia.edu
Subject: Re: picking max from list
In-Reply-To: <20031210205340.GA18726@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox