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?
Requires Free Membership to View
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.
This was first published in April 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation