3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

``For each article, find the dealer(s) with the most expensive price.''

In SQL-99 (and MySQL Version 4.1 or greater), I'd do it with a subquery like this:

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

In MySQL versions prior to 4.1 it's best do it in several steps:

1. Get the list of (article,maxprice).
2. For each article get the corresponding rows that have the stored maximum price.

This can easily be done with a temporary table:

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

If you don't use a TEMPORARY table, you must also lock the 'tmp' table.

``Can it be done with a single query?''

Yes, but only by using a quite inefficient trick that I call the ``MAX-CONCAT trick'':

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The last example can, of course, be made a bit more efficient by doing the splitting of the concatenated column in the client.

User Comments

It seems there are 2 errors in the example query
for "max concat trick". Am I missing something,
or should the code instead be:

SUBSTRING( MAX( CONCAT(RPAD
(price,6,' '),dealer) ), 6) AS dealer,

Note the use of RPAD with spaces instead of LPAD
with zeros, so that negative prices (begin with -
sign) are supported (assuming Unix standard strtod
(3) string to conversion). And note that
substring index should be 6 instead of 7, because
the padded price string has a length of 6
(occupies indices 0 - 5) in string. Certainly
substring() uses zero based indices.

I really dont see any reason why the second
CONCAT line is there at all. The goal is to select
the Most expensive dealer for each article, so
after you recieve that, if you really want to display
the price, all you have to do is
,MAX(price) AS price
It will be sorted with the dealer by the GROUP BY
statement. No need to make things more
complicated than they have to be.
This whole thing is an ugly workaround, but it
seems like it's the only way to do it, and I'm
surprised this method isnt mentioned and linked
more often. It seems like something very important
which MySQL is lacking.

That's a very helpful workaround (ugly though). I've
used this "method" to gather the last available day
of every month (and the id of the row). It worked,
but I wish there was a better way to do it ;(

select max(concat(rpad(poll,20,','),id)) from
reports_data where reportID=3 and circuitID=4
group by month(poll);

This whole section seems misdirected. There is a simple way to get the same information without resorting to 4.1 or max hacks:

SELECT shop.*
FROM shop LEFT JOIN shop AS s2
ON shop.article=s2.article AND shop.price<s2.price
WHERE s2.article IS NULL

What's happening is that the first clauses of the ON is matching up articles. The second one does the work. Notice that when shop.price is at a maximum (for a given article), there is no s2.price that is greater than it - the s2 entries for that row will be NULL. And those are exactly the rows that the WHERE clause grabs.

An application:

Suppose I have a (bug or inventory) Tracker table with columns:
Item, Id, Status, Value

Id is an auto_increment field, and it is in the primary key of (Item, Id). Status is a string - either 'Deleted' or something else. Over time, the values, Status (or other fields) associated with an Item change and each time this happens, we increment the Id field. We might remove an item from circulation by marking it Deleted.

To find the most recent entry (the entry with the highest Id) for each Item, where the Item does not currently have a Deleted status, we could use:

SELECT t1.*
FROM Tracker AS t1 LEFT JOIN Tracker AS t2
ON t1.Item=t2.Item AND t1.Id<t2.Id
WHERE t2.Id IS NULL AND t1.Status!='Deleted'

Enjoy,
Csaba Gabor from New York

Thank you Csaba Gabor! Your method works flawlessly!
In our case, we needed to assign jobs on a rotation basis by date. In rare cases, a worker may do more than one job on one date, so we handled the multiple date issue by using DISTINCTROW as follows:

SELECT DISTINCTROW jobs.worker
FROM jobs LEFT JOIN jobs AS s2
ON jobs.worker = s2.worker AND jobs.jobdate < s2.jobdate
WHERE s2.worker IS NULL
ORDER BY inspect.date ASC;

After running the above SQL, a drop-down displays a list of workers, with the worker in most need of a job at the top of the list. It works great!

Ralf in North Dakota

Csaba Gabor, your method works great!!! Thanks so much for saving me from that horrible temp table kludge :D

Add your own comment.