Re: select min row in a group

On Fri, 28 Jun 2002 17:56:17 -0400, Gregory Seidman
<gss+pg@cs.brown.edu> wrote:
>This is really an SQL question, not specific to PostgreSQL.
Greg,

but the answer is specific to PostgreSQL :-)

>For each ext_id (which references something in another table), I want to
>find the row with the minimum value.
>I feel like there has to be a way to do it without a nested query, probably
>using GROUP BY. Any thoughts?

If you only need ext_id and value, this should work with most SQL
dialects:

    SELECT ext_id, min(value)
    FROM ValTable
    GROUP BY ext_id;

If you need all the other columns, use this PostgreSQL extension:

    SELECT DISTINCT ON (ext_id) ext_id, value, ...
    FROM ValTable
    ORDER BY ext_id, value;

Servus
 Manfred