Rechercher dans le manuel MySQL

3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column

Contents

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

  1. SELECT article, dealer, price
  2. FROM   shop s1
  3. WHERE  price=(SELECT MAX(s2.price)
  4.               FROM shop s2
  5.               WHERE s1.article = s2.article)
  6. ORDER BY article;
  7.  
  8. +---------+--------+-------+
  9. | article | dealer | price |
  10. +---------+--------+-------+
  11. |    0001 | B      |  3.99 |
  12. |    0002 | A      | 10.99 |
  13. |    0003 | C      |  1.69 |
  14. |    0004 | D      | 19.95 |
  15. +---------+--------+-------+

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.11.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause, a LEFT JOIN, or a common table expression with a window function.

Uncorrelated subquery:

  1. SELECT s1.article, dealer, s1.price
  2. FROM shop s1
  3. JOIN (
  4.   SELECT article, MAX(price) AS price
  5.   FROM shop
  6.   GROUP BY article) AS s2
  7.   ON s1.article = s2.article AND s1.price = s2.price
  8. ORDER BY article;

LEFT JOIN:

  1. SELECT s1.article, s1.dealer, s1.price
  2. FROM shop s1
  3. LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
  4. WHERE s2.article IS NULL
  5. ORDER BY s1.article;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and thus the corresponding s2.article value is NULL. See Section 13.2.10.2, “JOIN Syntax”.

Common table expression with window function:

  1. WITH s1 AS (
  2.    SELECT article, dealer, price,
  3.           RANK() OVER (PARTITION BY article
  4.                            ORDER BY price DESC
  5.                       ) AS `Rank`
  6.      FROM shop
  7. )
  8. SELECT article, dealer, price
  9.   FROM s1
  10.   WHERE `Rank` = 1
  11. ORDER BY article;

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-example-maximum-column-group-row.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut