EXPERT RESPONSE
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.
|
