Выберите несколько максимальных значений

Вы можете использовать производную таблицу следующим образом:

SELECT    o1.item_id, o1.max_price, o2.user_id user_of_max_price
FROM      (
             SELECT item_id, MAX(price) max_price
             FROM `order`
             GROUP BY item_id
          ) o1
JOIN      `order` o2 ON (o2.price = o1.max_price AND o2.item_id = o1.item_id)
GROUP BY  o1.item_id;

Прецедент:

CREATE TABLE `order` (user_id int, item_id int, price decimal(5,2));

INSERT INTO `order` VALUES (1, 1, 10);
INSERT INTO `order` VALUES (1, 2, 15);
INSERT INTO `order` VALUES (1, 3, 8);
INSERT INTO `order` VALUES (2, 1, 20);
INSERT INTO `order` VALUES (2, 2, 6);
INSERT INTO `order` VALUES (2, 3, 15);
INSERT INTO `order` VALUES (3, 1, 18);
INSERT INTO `order` VALUES (3, 2, 13);
INSERT INTO `order` VALUES (3, 3, 10);

Результат:

+---------+-----------+-------------------+
| item_id | max_price | user_of_max_price |
+---------+-----------+-------------------+
|       1 |     20.00 |                 2 |
|       2 |     15.00 |                 1 |
|       3 |     15.00 |                 2 |
+---------+-----------+-------------------+
3 rows in set (0.00 sec)

Может быть, это немного дольше, но вы получаете читабельность

SELECT
        *
FROM
    `order`
JOIN
    (
        SELECT 
            item_id, 
            MAX(price) price 
        FROM 
            `order` 
        GROUP BY 
            item_id
    ) 
    USING(item_id, price);

Вам нужно сначала получить максимальную цену для каждого идентификатора товара, а затем вернуться к order, чтобы получить записи, в которых товар был заказан по максимальной цене. Что-то вроде следующего запроса должно работать. Хотя он вернет все записи с максимальными ценами предметов.

SELECT user_id, item_id, price
FROM order o
JOIN (
        SELECT item_id, max(price) max_price
        FROM order
        GROUP BY item_id
     ) o2 
  ON o.item_id = o2.item_id AND o.price = o2.max_price;

Это максимальный вопрос для каждой группы. Существуют различные подходы к этой распространенной проблеме. В MySQL обычно быстрее и проще использовать нулевое самосоединение, чем что-либо, связанное с подзапросами:

SELECT o0.user_id, o0.item_id, o0.price
FROM order AS o0
LEFT JOIN order AS o1 ON o1.item_id=o0.item_id AND o1.price>o0.price
WHERE o1.user_id IS NULL

т.е. «выбрать каждую строку, в которой нет другой строки для того же товара с более высокой ценой».

(Если две строки имеют одинаковую максимальную цену, вы вернете обе. Что именно делать в случае ничьей — это общая проблема для решений с максимальным значением для каждой группы.)

SELECT user_id, item_id, MAX(price) 
FROM order
GROUP BY item_id

Используемый вами SQL противоречит GROUP. Как только вы используете GROUP, MYSQL всегда будет выбирать ПЕРВЫЙ user_id, но НАИБОЛЕЕ ВЫСОКУЮ цену, это причина, по которой пользователь ошибается, а цена правильная.

Вы можете попробовать добавить ORDER BY price DESC, чтобы посмотреть, что произойдет, но я не пробовал в своей среде.

Ваш запрос группирует строки по item_id. Если у вас есть несколько товаров с item_id 1 и с разными user_id, будет выбран только первый user_id, а не user_id с самой высокой ценой.

Вам нужно либо сгруппировать по item_id И user_id (показывая максимальную цену за элемент для каждого пользователя), либо, если вы хотите, чтобы в группе был только элемент, вам нужно переосмыслить столбец user_id. например показать максимальную цену товара и показать ПОСЛЕДНЕГО пользователя, изменившего цену, ИЛИ показать максимальную цену за товар и показать пользователя, который СДЕЛАЛ максимальную цену за товар и т. д. Взгляните на этот пост для некоторых шаблонов для этого .

если вы хотите топ-2 из заказа, попробуйте это ...

если вы хотите топ-3, просто измените последнее условие, где item_rank in (1,2) ; на where item_rank in (1,2,3) ;

select * from 
    (select  item_id  , price
    , @curRow % @curNval  as item_rank
    ,  @curRow := @curRow + 1 AS row_number
    from `order` ,   (SELECT @curRow := 1 , @curNval := 3 ) r  
    order by  item_id , price desc  ) tab  where item_rank in (1,2) ;