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

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 - 00:47:08 CET