how do I write a mysql query to select the max of each group with conditions?
there are 3 tables. There's already a query, but I need to select all articles with the maximum ratings from all sources (categories) in a single mysql query
SELECT
mn2.max_r,mn.rating,
mn.*,mnc.news_category_name,
mnc.long_name,
u.username,
u.first_name,
u.last_name
FROM (
SELECT MAX( nnnn.rating) max_r FROM news nnnn
LEFT OUTER JOIN news_categories mnc1 ON mnc1.news_category_id = nnnn.news_category_id
WHERE (upper(mnc1.long_name) like upper('category 1'))
AND nnnn.STATUS = 'published' AND (DAY(nnnn.news_date) = '1') AND (MONTH(nnnn.news_date) = '1') AND (YEAR(nnnn.news_date) = '2013')
GROUP BY id_source
) mn2
INNER JOIN news mn
ON ( mn2.max_r = mn.rating)
LEFT OUTER JOIN news_categories mnc ON mnc.news_category_id = mn.news_category_id
LEFT OUTER JOIN users u ON u.user_id = mn.author_id
WHERE mn.status = 'published' AND (upper(mnc.long_name) like upper('category 1'))
AND ( DAY( mn.news_date ) = '1') AND ( MONTH ( mn.news_date ) = '1') AND ( YEAR ( mn.news_date ) = '2013')
GROUP BY id_source
ORDER BY rating DESC
the first nested query generates all the maximums by condition, but with the wrong article numbers,
then a join runs against it and picks out the correct records... matching the maximum, so as not to count duplicate or zero ratings that get used twice
GROUP BY id_source
Comments