MySQL 首先 GROUP BY，然后 ORDER

Question

我一直在搜索，但无法找到合适的解决方案来对我的表格“商店”进行排序。

它是这样的:

Product           Price
---------------------------------
Site 1             35
Site 2             50
Site 3             15
Site 1             30
Site 2              5

现在我需要它看起来像这样:

Product           Price
---------------------------------
Site 2             50
Site 2              5
Site 1             35
Site 1             30
Site 3             15

表格应从最高价格开始排序，然后按产品分组。我尝试了一百万个不同的查询，我得到的最接近的是:

SELECT m.* FROM shop m
    INNER JOIN
        (SELECT product, MAX(price) AS maxprice FROM shop GROUP BY product ORDER BY maxprice ASC) s
    ON m.product = s.product
ORDER BY s.maxprice DESC

查询完成了它的工作，但以错误的方式对组中的价格进行了排序。

Product           Price
---------------------------------
Site 2              5
Site 2             50
Site 1             30
Site 1             35
Site 3             15

我做错了什么？非常感谢您的帮助!

最好，感谢一百万!

Answer 1

Select x.product, x.price from 
(Select product, max(price) as mprice from shop
group by product) as tbl inner join shop x on tbl.product = x.product
order by tbl.mprice desc, x.Price desc

我还注意到你创建了一个 fiddle 可以节省我一些时间，但这是更新 fiddle

SELECT s.product, s.Price
from (Select product, max(price) as mprice
   from shop group by product) as tbl
   inner join shop s on s.product = tbl.product
order by tbl.mprice desc, s.price desc

http://sqlfiddle.com/#!2/c5eb64/3