CODEWITHSUNDEEP
mysqlsql
emcedi
emcedi

Reputation: 13

MySQL First GROUP BY, then ORDER

I've been searching all over, but couldn't come up with a proper solution to sort my table 'shop'.

This is how it looks:

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

Now I need it to look like this:

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

The table should be sorted starting with the highest price and then grouping it by the product. I tried a million different queries and the closest I got was this:

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

The query does it's job but sorts the prices in the group the wrong way around.

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

What am I doing wrong? Help is much appreciated!

Best and thanks a million!

Upvotes: 1

Views: 290

Answers (2)

user3277102
user3277102

Reputation: 76

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

I also notice you created a fiddle would have saved me some time but here is the update 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

Upvotes: 1

Sam DeHaan
Sam DeHaan

Reputation: 10325

You've got two levels of sorting, so you need to describe both in your ORDER BY

ORDER BY s.maxprice DESC, m.price DESC

Upvotes: 0

Related Questions