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