I have two MySQL tables, products and sites with a common key of siteId:
products productID productName productPopularity siteId sites siteID siteTitle
I wish to select the top two products (based on productPopularity) for each site that has entries in the products table, starting with the site that has the most popular product. Can you advise please?
There are two parts to this problem, and I don't have a good answer for either.
To get the top 2 products for each site, you can use this query:
select S.siteID , S.siteTitle , P1.productPopularity from sites S inner join products P1 on S.siteID = P1.siteID inner join products P2 on S.siteID = P2.siteID and P1.productPopularity <= P2.productPopularity group by S.siteID , S.siteTitle , P1.productPopularity having count(*) <= 2 order by S.siteID , P1.productPopularity desc
However, this query will not return correct results for a given site if there happens to be a tie for either first or second place. Sorry, but that's just the way it is. There's no easy way around it, either, unless you can wait for MySQL version 4.1, which supports subqueries:
select S.siteID , P.productName , P.productPopularity from sites S inner join products P on S.siteID = P.siteID where P.productPopularity = ( select max(productPopularity) from products where siteID = S.siteID ) or P.productPopularity = ( select max(productPopularity) from products where siteID = S.siteID and productPopularity < ( select max(productPopularity) from products where siteID = S.siteID ) ) order by S.siteID , P.productPopularity desc
In this query, products are chosen that have the highest popularity in the site, or the highest popularity that is less than the highest, i.e. the second highest. Note that ties are handled correctly, but you may get more than two rows per site.
As for sorting the site with the highest overall popular product first, that's a twist that you cannot solve with a simple ORDER BY.
Seems to me that in order to get exactly what you want, in MySQL prior to version 4.1, you will have to run several queries and create temp tables. The "site with the most popular product first" requirement means that you will probably need an additional temp table just for that.
Just be sure to use good examples in your test cases, i.e. 3-way tie for first place, or 2-way tie for second place.