pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL dealing with subquery
4+ messages / 2 participants
[nested] [flat]
* SQL dealing with subquery
@ 2008-01-15 18:04 Bryan Emrys <bryan.emrys@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bryan Emrys @ 2008-01-15 18:04 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I'm having a conceptual problem with a subquery here - any help would be
appreciated.
I have a table treaty_rates with columns payor, payee, rate where payor and
payee are countries.
Sample set:
'US','UK',5
'US','Ireland',5
'US','Netherlands',5
'US','China',10
'Canada','US',0
'Canada','Ireland',0
'Canada','Netherlands',5
'Canada','UK,5
'Belgium','Netherlands',0
There is no certainty that the same payees exist for each payor (i.e. not
all countries have treaties with other all other countries)
I want to select all rows where the payee is in some array (e.g, in
('Netherlands','Ireland')) and the rate is not the same
In other words, in the sample above, I only want to return:
'Canada','Ireland',0
'Canada','Netherlands',5
The initial query to limit the rows to the specified payees is obvious, but
I can't get my mind around how to compare each set of rows where the payor
is the same country. Running select payor, payee, rate where payee in
('Netherlands','Ireland') will return 132 rows (66 pairs).
Any suggestions on how to compare the pairs would be appreciated.
Bryan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL dealing with subquery
@ 2008-01-15 20:40 Rodrigo E. De León Plicet <rdeleonp@gmail.com>
parent: Bryan Emrys <bryan.emrys@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Rodrigo E. De León Plicet @ 2008-01-15 20:40 UTC (permalink / raw)
To: Bryan Emrys <bryan.emrys@gmail.com>; +Cc: pgsql-sql
On Jan 15, 2008 1:04 PM, Bryan Emrys <bryan.emrys@gmail.com> wrote:
> In other words, in the sample above, I only want to return:
> 'Canada','Ireland',0
> 'Canada','Netherlands',5
Try (untested):
SELECT t2.*
FROM (SELECT payor
FROM treaty_rates
WHERE payee IN ('Netherlands', 'Ireland')
GROUP BY payor
HAVING MIN (rate) != MAX (rate)) t1
JOIN
treaty_rates t2 ON t1.payor = t2.payor
WHERE t2.payee IN ('Netherlands', 'Ireland');
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL dealing with subquery
@ 2008-01-16 15:54 Bryan Emrys <bryan.emrys@gmail.com>
parent: Rodrigo E. De León Plicet <rdeleonp@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bryan Emrys @ 2008-01-16 15:54 UTC (permalink / raw)
To: Rodrigo E. De León Plicet <rdeleonp@gmail.com>; +Cc: pgsql-sql
Thanks. It throws off a few extra countries where there is only one treaty, but those are
few enough that I can handle them manually.
I thought the solution was also going to give me insight into how to select just the lowest
rate from each couple, (i.e. for each payor, who is the lowest rate payee) but it looks like
I'll have find some time to think about that later (I've also got to think about what to do in tie situations.).
Again, thanks.
Bryan
On Tuesday 15 January 2008 12:40:13 pm Rodrigo E. De León Plicet wrote:
> On Jan 15, 2008 1:04 PM, Bryan Emrys <bryan.emrys@gmail.com> wrote:
> > In other words, in the sample above, I only want to return:
> > 'Canada','Ireland',0
> > 'Canada','Netherlands',5
>
> Try (untested):
>
> SELECT t2.*
> FROM (SELECT payor
> FROM treaty_rates
> WHERE payee IN ('Netherlands', 'Ireland')
> GROUP BY payor
> HAVING MIN (rate) != MAX (rate)) t1
> JOIN
> treaty_rates t2 ON t1.payor = t2.payor
> WHERE t2.payee IN ('Netherlands', 'Ireland');
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL dealing with subquery
@ 2008-01-16 17:34 Bryan Emrys <bryan.emrys@gmail.com>
parent: Bryan Emrys <bryan.emrys@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bryan Emrys @ 2008-01-16 17:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-01-16 17:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-15 18:04 SQL dealing with subquery Bryan Emrys <bryan.emrys@gmail.com>
2008-01-15 20:40 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
2008-01-16 15:54 ` Bryan Emrys <bryan.emrys@gmail.com>
2008-01-16 17:34 ` Bryan Emrys <bryan.emrys@gmail.com>
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