Top two in each group in MySQL

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.