- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
Additional languages
MySQL 5.1 Reference Manual :: 3 Tutorial :: 3.6 Examples of Common Queries :: 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field
Section Navigation [Toggle]
- 3.6 Examples of Common Queries
- 3.6.1 The Maximum Value for a Column
- 3.6.2 The Row Holding the Maximum of a Certain Column
- 3.6.3 Maximum of Column per Group
- 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field
- 3.6.5 Using User-Defined Variables
- 3.6.6 Using Foreign Keys
- 3.6.7 Searching on Two Keys
- 3.6.8 Calculating Visits Per Day
- 3.6.9 Using AUTO_INCREMENT
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:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
The preceding example uses a correlated subquery, which can be
inefficient (see Section 12.2.8.7, “Correlated Subqueries”). Other
possibilities for solving the problem are to use a uncorrelated
subquery in the FROM clause or a
LEFT JOIN:
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;
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 the
s2 rows values will be
NULL. See Section 12.2.7.1, “JOIN Syntax”.
© 1995-2008 MySQL AB. All rights reserved.
User Comments
OK, Some BAD News about performance:
If you're accessing your MySQL via PHP, (and possibly other programming languages) which many of us are, each SQL statement has to be run separately (i.e. using mysql_query($qry,$db);).
This means that the temporary table method requires *5* separate calls to 'mysql_query()', whereas Csaba's method only takes one!
I've performance tested these methods on my own CMS (TC! http://top-cat.com) using XDebug (http://www.xdebug.org/) and found these results for running a function which displays the most recent version of a list of items:
TEMP TABLE METHOD: 12.8439433575 s
CSABA'S METHOD: 11.8751540184 s
What's WORSE is this is the result when I use a separate PHP function to get the max version:
SEPARATE FUNCTION METHOD: 7.6197450161 s
... This means you're better off creating a separate PHP function to get the max value, THEN building it into the query... Whichever way you do it - it's slow!
If you can change your coding to set a separate column like 'status' to 'C'urrent or 'M'ax in each row - this is the quickest way to get the data (although setting it obviously becomes slower). If I get the first version of all the items, this is the timing I get (it may be quicker if you index the column too):
NO-JOIN METHOD: 6.4667682648 s
MAX CONCAT METHOD: ... I'm afraid I couldn't get this to work at all.
It is important to note how multiple dealers tied for the best price are treated differently. In the sub-select, all dealers with the best price are selected. But using the max-concat, only one will be, namely the one that wins alphabetically.
another variation of the self-join (which, after all, is what the database engine executes for a correlated subquery, no?)
this self-join uses GROUP BY, thus allowing aggregate functions
example using the shop table:
select s1.article
, s1.dealer
, s1.price
, count(*) as articles
from shop s1
inner
join shop s2
on s1.dealer = s2.dealer
group
by s1.article
, s1.dealer
, s1.price
having s1.price = max(s2.price)
results:
article dealer price articles
1 B 3.99 2
2 A 10.99 2
3 C 1.69 1
4 D 19.95 2
LEFT JOIN first appears here in the tutorial with little explanation.
groupwise MAX
19 rows in set (0.22 sec)<quoted-from-site>:
A good solution to "Get row with the group-wise maximum" Getting just the maximum for the group is simple, getting the full row which is belonging to the maximum is the interesting step.
</quoted-from-site>
The solution was found at the link below. I thought it will be helpful to others like it was to me after spending couple days to get working (I am a noob). It is a great read (must read) to anyone and has multiple (10) different solutions. I will check all of them to find the fastest and add more info. to this post later.
http://jan.kneschke.de/projects/mysql/groupwise-max
Here is my elaborate example:
To list all records without filtering for the MAX and verify the final result only:
NOTICE HOW THE 2ND TABLE HAS THE RECORDS WITH MAXIMUM RevNo ONLY
SELECT DrawingID,DrawingNo,SheetNo,RevNo FROM Drawings WHERE (`DrawingNo` LIKE 'to-10-D%');
Here is the statement that got me the good result I was expecting. I did the test with different search criteria to make sure and all worked perfectly.
SELECT D1.DrawingID,D1.DrawingNo,D1.SheetNo,D1.RevNo
FROM Drawings AS D1, (SELECT DrawingID,DrawingNo,SheetNo,MAX(RevNo) AS MaxRevNo
FROM Drawings WHERE (`DrawingNo` LIKE 'to-10-D%') GROUP BY DrawingNo,SheetNo) AS D2
WHERE D2.DrawingNo=D1.DrawingNo AND D2.SheetNo=D1.SheetNo AND D1.RevNo=D2.MaxRevNo;
16 rows in set (0.81 sec)
Add your own comment.