(mysql.info.gz) example-Maximum-row

Info Catalog

(mysql.info.gz) example-Maximum-column

(mysql.info.gz) Examples

(mysql.info.gz) example-Maximum-column-group

 
 3.6.2 The Row Holding the Maximum of a Certain Column
 -----------------------------------------------------
 
 "Find number, dealer, and price of the most expensive article."
 
 In standard SQL (and as of MySQL 4.1), this is easily done with a
 subquery:
 
      SELECT article, dealer, price
      FROM   shop
      WHERE  price=(SELECT MAX(price) FROM shop);
 
 In MySQL versions prior to 4.1, just do it in two steps:
 
   1. Get the maximum price value from the table with a `SELECT'
      statement.
           mysql> SELECT MAX(price) FROM shop;
           +------------+
           | MAX(price) |
           +------------+
           |      19.95 |
           +------------+
 
   2. Using the value 19.95 shown by the previous query to be the maximum
      article price, write a query to locate and display the
      corresponding record:
           mysql> SELECT article, dealer, price
               -> FROM   shop
               -> WHERE  price=19.95;
           +---------+--------+-------+
           | article | dealer | price |
           +---------+--------+-------+
           |    0004 | D      | 19.95 |
           +---------+--------+-------+
 
 Another solution is to sort all rows descending by price and only get
 the first row using the MySQL-specific `LIMIT' clause:
 
      SELECT article, dealer, price
      FROM   shop
      ORDER BY price DESC
      LIMIT 1;
 
 Note:  If there were several most expensive articles, each with a price
 of 19.95, the `LIMIT' solution would show only one of them!
 

Info Catalog

(mysql.info.gz) example-Maximum-column

(mysql.info.gz) Examples

(mysql.info.gz) example-Maximum-column-group