3.5.4 Строка, содержащая максимальное значение некоторого столбца

"Для каждого изделия, как определить дилер(ов) с самыми высокими ценами?"

В ANSI SQL это легко делается при помощи вложенного запроса:

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

В MySQL такая задача выполняется в два этапа:

1. Следует получить список (изделие, максимальная цена)
2. Для каждого изделия, получить соответствующие записи, в которых цена соответствует максимальной.

Это легко делается с помощью временной таблицы:

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;

Если вы не используете ключевое слово TEMPORARY, вам также следует поставить блокировку на таблицу tmp.

"А можно ли это сделать одним запросом?"

Да, но только используя совершенно неэффективный трюк, который я называю "Трюк MAX-CONCAT":

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 |
+---------+--------+-------+

Разумеется, последний пример можно сделать чуть эффективнее, если разбиение катенизированной строки делать на стороне клиента.

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);

Add your own comment.