GNU Info

(mysql.info)example-Maximum-row

---

Next: example-Maximum-column-group Prev: example-Maximum-column Up: Examples

---

Enter node , (file) or (file)node

The Row Holding the Maximum of a Certain Column
-----------------------------------------------

"Find number, dealer, and price of the most expensive article."

In ANSI SQL this is easily done with a sub-query:

     SELECT article, dealer, price
     FROM   shop
     WHERE  price=(SELECT MAX(price) FROM shop)

In MySQL (which does not yet have sub-selects), just do it in two steps:

  1. Get the maximum price value from the table with a `SELECT'
     statement.

  2. Using this value compile the actual query:
          SELECT article, dealer, price
          FROM   shop
          WHERE  price=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 are several most expensive articles (for example,
each 19.95) the `LIMIT' solution shows only one of them!