select S1.Region, S1.Price from (select CompanyPrices.*, Region from
CompanyPrices inner join CompanyRegion on
CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join
CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2
on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region,
S1.Price
However, if I want to get 10 cheapest products for each company, the
above sql works by modifying the join condition. Instead of S1.Region
= S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct
results for 10 cheapest products for each company.
select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region
from CompanyPrices inner join CompanyRegion on
CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join
CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2
on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by
S1.CompanyID, S1.Price
Of course, the join in S1 and S2 with the CompanyRegion table is not
necessary in the second query.