MySQL select DISTINCT by highest value

SELECT 
    p.*
FROM
        product p
    INNER JOIN
        ( SELECT 
              magazine, MAX(onSale) AS latest
          FROM
              product
          GROUP BY 
              magazine
        ) AS groupedp
      ON  groupedp.magazine = p.magazine
      AND groupedp.latest = p.onSale ;

SELECT id, MAX(onSale) as latest, magazine
FROM product
GROUP BY magazine
ORDER BY latest DESC

None of the given answers are correct, as they return an disassociated set of data that does not represent one exact row. The id may not be the id from the same row as the onsale value.

The following will work:

SELECT
    id, onsale, magazine
FROM (
    SELECT
       id, onsale, magazine
    FROM
        product
    ORDER BY
        onsale DESC) AS a
GROUP BY
    magazine

Tags:

Mysql