Bug #25696 Alternatives to rows with group-wise maximum

• Submitted: 18 Jan 2007 15:22
• Modified: 5 Mar 2007 19:54
• Reporter: Scott Noyes (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S4 (Feature request)
• Assigned to: Paul DuBois
• CPU Architecture: Any

[18 Jan 2007 15:22] Scott Noyes

Description:
The suggested query for fetching "The Rows Holding the Group-wise Maximum of a Certain Field" uses a collated subquery in the WHERE clause, which makes it inefficient. I'd like to see two other recommendations listed, those of creating a derived table or unnamed view, and that of using a simple LEFT JOIN with no subquery, as recommended in the user comments by Csaba Gabor.

How to repeat:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Suggested fix:
Mention the problems inherent to a correlated subquery, and add these two recommendations:

SELECT s1.article, dealer, s1.price
FROM   shop s1
JOIN (
  SELECT article, MAX(price) AS price 
  FROM shop 
  GROUP BY article
) AS s2 ON s1.article = s2.article AND s1.price = s2.price;

SELECT s1.article, s1.dealer, s1.price
FROM   shop s1
LEFT JOIN shop s2 ON (s1.article = s2.article AND s1.price < s2.price)
WHERE  s2.article IS NULL;

[5 Mar 2007 19:54] Paul DuBois

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.