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

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

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
Received on Sun Jan 19 2003 - 20:53:54 CET