Re: select top n results in a group; count(*)<=n, count smallest largest etc. problem

You should be able to do this more efficiently by using analytic functions. The solution should look something like this:

SELECT region,

       price
FROM (SELECT Region,

               Price,
               row_number OVER (PARTITION BY Region ORDER BY Price) as
r
        FROM   CompanyPrices p,
               CompanyRegion r
        WHERE  p.CompanyID = r.CompanyID)

gudia97_at_yahoo.com (gudia) wrote in message news:<2130f7ff.0301191547.149af844_at_posting.google.com>...
> tonkuma_at_jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0301191153.74d57ba7_at_posting.google.com>...
> > I can't beleive the second query worked well.
> > Because it seems that the query selects the companies that sell less
> > than 10 products and each prices of these company.
> > I feel you should use S1.Region = S2.Region AND S1.Price >= S2.Price,
> > like this:
> > 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
> > AND S1.Price >= S2.Price
> > group by S1.Region, S1.Price
> > having count(*) <= 10
> > order by S1.Region, S1.Price
> >
> > For the second query, use S1.CompanyID = S2.CompanyID AND S1.Price >=
> > S2.Price.
> >
> > gudia97_at_yahoo.com (gudia) wrote in message news:<2130f7ff.0301190742.27dd929d_at_posting.google.com>...
> > > Two tables: CompanyPrices(CompanyID, ProductID, Price),
> > > CompanyRegion(CompanyID, Region)
> > > ProductID is the primary key.
> > >
> > > I want to get 10 smallest prices in each Region. In other words, I am
> > > looking for 10 cheapest prices in each region. So, if there are 20
> > > regions, I should get excatly 200 rows having prices for products from
> > > 200 companies if there were at least 10 companies in each region.
> > >
> > > I tried the follwoing, but get incorrect results.
> > >
> > > 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.
> > >
> > > I am not sure what is wrong in the first query and why it does not
> > > work when the second one works. Could someone please help in making
> > > the first query work to give me correct results?
> > >
> > > Thanks
>
> Tokunaga,
>
> You are absolutely right. It was a mistake. The results appeared to be
> right in the first glance. But, you are right that S1.Price >=
> S2.Price needs to be added to make both queries work correctly.
>
> Thanks so much
Received on Mon Jan 20 2003 - 17:23:10 CET