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
