Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.3Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.3Mb
HTML Download (RPM) - 5.3Mb
Man Pages (TGZ) - 157.6Kb
Man Pages (Zip) - 260.8Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  The Row Holding the Maximum of a Certain Column

3.6.2 The Row Holding the Maximum of a Certain Column

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

This is easily done with a subquery:

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

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

Other solutions are to use a LEFT JOIN or to sort all rows descending by price and get only the first row using the MySQL-specific LIMIT clause:

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

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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by eded eded on March 3, 2011
Maybe an optimized way to obtain the same results. Actually, opening the table only once :

SELECT MAX(CASE WHEN @price<=price THEN CONCAT(@price:=price,@article:=article,@dealer:=dealer) ELSE 0 END) AS dummy,
MAX(@article),
MAX(@dealer),
MAX(@price)
FROM
shop,
(SELECT @article:=0) r ,
(SELECT @dealer:=0) r2,
(SELECT @price:=0) r3
GROUP BY
@price:=-10,
@article:='',
@dealer:='';

Hopefully this method can be faster in some cases.

  Posted by Alexandru Iancu on October 30, 2012
Let's asume we have a table with:

client_id | call_back_hour | call_reason | comment

We want to extract only the last calls rescheduled(reason 2) with their comments from the table:

Here is the method i used:

SELECT
t1.id_client,
t1.or_res,
t2.`comment`,
call_reason
FROM
(SELECT
MAX(call_back_hour) as or_res,
id_client
from ai_calls
WHERE call_reason =2
GROUP BY id_client)
as t1
LEFT JOIN ai_calls as t2 on
t1.id_client=t2.id_client and t1.or_res=t2.call_back_hour
Sign Up Login You must be logged in to post a comment.