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

Re: SQL dealing with subquery

From: "Bryan Emrys" <bryan(dot)emrys(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: SQL dealing with subquery
Date: 2008-01-16 17:34:53
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Franklin HautDate: 2008-01-16 18:07:32
Subject: Re: Update PK Violation
Previous:From: Scott MarloweDate: 2008-01-16 16:15:48
Subject: Re: Update PK Violation

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