From: Bryan Emrys <bryan.emrys@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL dealing with subquery
Date: Wed, 16 Jan 2008 09:34:53 -0800
Message-ID: <eee4647d0801160934n3efe1e28h5a7c1e1cb5edf1ad@mail.gmail.com> (raw)
In-Reply-To: <200801160754.41769.bryan.emrys@gmail.com>
References: <eee4647d0801151004t288b0819ocbc0cae8e0df7f24@mail.gmail.com>
<a55915760801151240w61aa3520g548eff734190061e@mail.gmail.com>
<200801160754.41769.bryan.emrys@gmail.com>
Following up my treaty rate thoughts, if I'm trying to get the lowest treaty
payee (and rate) from a specific list of payees for every possible payor
country, the following seems to work, but is it right? I'm specifically
wondering about the group by clauses. (Or if there is a better way.) [table
treaties has columns payor,payee,rate and in this sample, I'm just trying to
find which country payee between Ireland and the Netherlands has the lowest
rate from each individual payor country.]
select a.payor,a.payee,a.rate
from treaties a,
(select payor,min(rentr) from treaties
where payee in ('Ireland','Netherlands') group by payor
) b
where a.payor=b.payor
and a.rate = b.min
and payee in ('Ireland','Netherlands')
group by a.payor, a.payee, a.rate
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: bryan.emrys@gmail.com
Subject: Re: SQL dealing with subquery
In-Reply-To: <eee4647d0801160934n3efe1e28h5a7c1e1cb5edf1ad@mail.gmail.com>
* 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