Rank in subquery-CodePudding

I'm looking to report the best seller by total sales price using the table below: Sales table:

seller_id | quantity | price

| 1 | 2 | 2000 |

| 1 | 1 | 800 |

| 2 | 1 | 800 |

| 3 2 | 2800 |

The result need to be 1 & 3 (a table with 1 column "seller_id")since both sellers with id 1 and 3 sold products with the most total price of 2800

I tried to write a query using rank.. in a subquery, I will have something like:

SELECT sum(price), rank() over (order by price desc ) FROM sales group by seller_id

Then, I need to select the sum(price) with rank=1, but I don't know how to write that.. Then, outside of the subquery, I want to select just the seller_id. How do we do these?

CodePudding user response：

Use RANKING function for retrieving seller_id based on highest price. RANK() function serialize the position with a GAP. If two values in same position then third value position after this to value will 3 not 2.

-- MySQL (v5.8)
SELECT t.seller_id, t.total_price
FROM (SELECT seller_id, SUM(price) total_price
      , RANK() OVER (ORDER BY SUM(price) DESC) rank_price
      FROM sales
      GROUP BY seller_id) t
WHERE t.rank_price = 1

DENSE_RANK() function serialize the position without a GAP. If top 5 price needed in future then it'll better.

-- MySQL (v5.8)
SELECT t.seller_id, t.total_price
FROM (SELECT seller_id, SUM(price) total_price
      , DENSE_RANK() OVER (ORDER BY SUM(price) DESC) rank_price
      FROM sales
      GROUP BY seller_id) t
WHERE t.rank_price = 1

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=90c756fa6d3ab25914721cfe40df5e9a