Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DFF262E3962
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 16 Jan 2008 13:35:01 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 69578-02-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 16 Jan 2008 13:34:56 -0400 (AST)
Received: from nz-out-0506.google.com (nz-out-0506.google.com
 [64.233.162.227])
	by postgresql.org (Postfix) with ESMTP id B9AD92E38DD
	for <pgsql-sql@postgresql.org>; Wed, 16 Jan 2008 13:34:55 -0400 (AST)
Received: by nz-out-0506.google.com with SMTP id l8so240294nzf.28
	for <pgsql-sql@postgresql.org>; Wed, 16 Jan 2008 09:34:54 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	bh=q5ubwMTWJcUaunP8OCi6xrS3fQpivq7vvz4e7iDcpio=;
	b=VCUbJuOs+00R0qcddGNHGE5C4Wm76hz/v1y7T1Y+zHTWpfDUfh3et9H+XF+FIitvnHkj/sC8bhpt6z7Gcwgtdj2MAylgSettZi35CqroYDT3FbWUflqDckRLxMTHkmscGN49oLDsSSZJ9nD6MLPYWVU1pkpFuuW2z8oxkCeFtko=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=QqbNQFYYyc5T7ydvE90kTsudJ/UUiEO0rIygGK1l6vN4XtVqgmi8P0hbBcp+ZzxfZr9pOwegvhgTIoCJnwfbPmm1SJcuSAr51ESKKfw1DFLb/NdkZQORwbhcg6Dlq8c/+cyNhe0Hq3hydzo7rZNr354HWhlval/dfPUc94AgZwQ=
Received: by 10.141.75.6 with SMTP id c6mr719012rvl.272.1200504893523;
	Wed, 16 Jan 2008 09:34:53 -0800 (PST)
Received: by 10.141.84.8 with HTTP; Wed, 16 Jan 2008 09:34:53 -0800 (PST)
Message-ID: <eee4647d0801160934n3efe1e28h5a7c1e1cb5edf1ad@mail.gmail.com>
Date: Wed, 16 Jan 2008 09:34:53 -0800
From: "Bryan Emrys" <bryan.emrys@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL dealing with subquery
In-Reply-To: <200801160754.41769.bryan.emrys@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_22006_25444281.1200504893517"
References: <eee4647d0801151004t288b0819ocbc0cae8e0df7f24@mail.gmail.com>
	<a55915760801151240w61aa3520g548eff734190061e@mail.gmail.com>
	<200801160754.41769.bryan.emrys@gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/144
X-Sequence-Number: 29997

------=_Part_22006_25444281.1200504893517
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_22006_25444281.1200504893517
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br>Following up my treaty rate thoughts, if I&#39;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&#39;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&#39;m just trying to find which country payee between Ireland and the Netherlands has the lowest rate from each individual payor country.]
<br><br>select a.payor,a.payee,a.rate<br>from treaties a,<br><br>(select payor,min(rentr) from treaties<br>where payee in (&#39;Ireland&#39;,&#39;Netherlands&#39;) group by payor<br>) b<br><br>where a.payor=b.payor<br>and 
a.rate = b.min<br>and payee in (&#39;Ireland&#39;,&#39;Netherlands&#39;)<br>group by a.payor, a.payee, a.rate<br>

------=_Part_22006_25444281.1200504893517--