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

Re: select min row in a group

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: gss+pg(at)cs(dot)brown(dot)edu
Cc: PostgreSQL general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: select min row in a group
Date: 2002-06-28 22:27:41
Message-ID: 48ophu8t7k4rsklf3go4fp69hmkqoslptp@4ax.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, 28 Jun 2002 17:56:17 -0400, Gregory Seidman
<gss+pg(at)cs(dot)brown(dot)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



In response to

Responses

pgsql-general by date

Next:From: Manfred KoizarDate: 2002-06-28 22:53:55
Subject: Re: serial columns & loads misfeature?
Previous:From: Gregory SeidmanDate: 2002-06-28 22:16:30
Subject: Re: serial columns & loads misfeature?

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