Given a table products

and a table sales

How would I be able to get the product(s) with the most sold units? My goal is to run a SELECT statement that results in, for this example,

because the sum of sold units of both those products is 12, the maximum value (greater than 4 for Tea, despite there being more sales for Tea).

I have the following query:

SELECT DISTINCT products.pid, products.name
FROM sales
    INNER JOIN products ON sale.pid = products.pid
    INNER JOIN (
        SELECT pid, SUM(units) as sum_units
        FROM sales
        GROUP BY pid
    ) AS total_units ON total_units.pid = sales.pid
WHERE total_units.sum_units IN (
    SELECT MAX(sum_units) as max_units
    FROM (
        SELECT pid, SUM(units) as sum_units
        FROM sales
        GROUP BY pid
    ) AS total_units
);

However, this seems very long, confusing, and inefficient, even repeating the sub-query to obtain total_units, so I was wondering if there was a better way to accomplish this.

How can I simplify this? Note that I can't use ORDER BY SUM(units) LIMIT 1 in case there are multiple (i.e., >1) products with the most units sold.

Thank you in advance.

CodePudding user response：

Since Postgres 13 it has supported with ties so your query can be simply this:

select p.pId, p.name
from sales s
join products p on p.pid = s.pid
group by p.pId, p.name
order by Sum(units) desc
fetch first 1 rows with ties;

See demo Fiddle

CodePudding user response：

Solution for your problem:

WITH cte1 AS
(
 SELECT s.pid, p.name,
 SUM(units) as total_units
 FROM sales s
 INNER JOIN products p
 ON s.pid = p.pid
 GROUP BY s.pid, p.name
),
cte2 AS
(
 SELECT *,
 DENSE_RANK() OVER(ORDER BY total_units DESC) as rn
 FROM cte1
)
SELECT pid,name
FROM cte2
WHERE rn = 1
ORDER BY pid;

Working example: db_fiddle link