Find the row with the highest value in SQL

Question

According to the schema, the Orders table has the following columns:

OrderId integer, CustomerId integer, RetailerId integer, ProductId integer, Count integer

I'm trying to figure out what product has the highest number of orders for each retailer.

So, to sum up all the orders per product, I have the following query:

SELECT RetailerId, ProductId, SUM(Count) AS ProductTotal
FROM Orders
GROUP BY RetailerId, ProductId
ORDER BY RetailerId, ProductTotal DESC;

This gives an output like this:

RETAILERID  PRODUCTID PRODUCTTOTAL
---------- ---------- ------------
         1          5          115
         1         10           45
         1          1           15
         1          4            2
         1          8            1
         2          9           12
         2         11           10
         2          7            1
         3          3            3
         4          2            1
         5         11            1

Now, all I want to do is find the product with the highest number of orders per retailer. Right now, it shows all the products; I want only one.

I have tried way too many things. Following is one particularly abhorrent example:

SELECT O.RetailerId, O.ProductId, O.ProductTotal
FROM (
  SELECT Orders.ProductId, MAX(ProductTotal) AS MaxProductTotal
  FROM (
    SELECT Orders.ProductId AS PID, SUM(Orders.Count) AS ProductTotal
    FROM Orders
    GROUP BY Orders.ProductId
  ) AS O INNER JOIN Orders ON Orders.ProductId = PID
  GROUP BY Orders.ProductId
) AS X INNER JOIN O ON O.RetailerId = X.RetailerId AND O.ProductTotal = X.MaxProductTotal;

The solution to this is probably the simplest thing ever, but I just can't right now. So, I'd like some help.

score 1 · Accepted Answer · answered Nov 08 '18 at 08:31

Select the maximum total per customer with a window function:

SELECT RetailerId, ProductId, ProductTotal
FROM
(
  SELECT
    RetailerId, ProductId, SUM(Count) AS ProductTotal,
    MAX(SUM(Count)) OVER (PARTITION BY RetailerId) AS MaxProductTotal
  FROM Orders
  GROUP BY RetailerId, ProductId
)
WHERE ProductTotal = MaxProductTotal
ORDER BY RetailerId;

score 0 · Answer 2 · answered Nov 08 '18 at 04:47

You can try using window function row_number()

select * from
(
select *,row_number() over(partition by RetailerId order by ProductTotal desc) as rn from
(
SELECT RetailerId, ProductId, SUM(Count) AS ProductTotal
FROM Orders
GROUP BY RetailerId, ProductId
)A
)X where rn=1

score 0 · Answer 3 · answered Nov 08 '18 at 05:03

you can use row_number() with cte

with cte as
(
   SELECT o.RetailerId,o.ProductId AS PID, SUM(o.Count) AS ProductTotal
    FROM Orders o
    GROUP BY o.ProductId,o.RetailerId
), cte2 as
(
select RetailerId,PID,ProductTotal, 
row_number() over(partition by RetailerId order by ProductTotal desc) as rn
from cte
) select RetailerId,PID,ProductTotal from cte2 where rn=1

score 0 · Answer 4 · answered Nov 08 '18 at 05:39

Have you tried using rank?

Try this query

select OrderC, retailer_id,product_id from ( select sum(count_order) as OrderC,retailer_id,product_id,row_number() over(order by OrderC desc) as RN from order_t  group  by retailer_id,product_id) as d where RN=1;

score 0 · Answer 5 · answered Nov 08 '18 at 07:31

Here is a version without sub-query using FIRST/LAST:

WITH t(RETAILERID, PRODUCTID, PRODUCTTOTAL) AS (    
    SELECT 1,  5,  115 FROM dual UNION ALL
    SELECT 1, 10,   45 FROM dual UNION ALL
    SELECT 1,  1,   15 FROM dual UNION ALL
    SELECT 1,  4,    2 FROM dual UNION ALL
    SELECT 1,  8,    1 FROM dual UNION ALL
    SELECT 2,  9,   12 FROM dual UNION ALL
    SELECT 2, 11,   10 FROM dual UNION ALL
    SELECT 2,  7,    1 FROM dual UNION ALL
    SELECT 3,  3,    3 FROM dual UNION ALL
    SELECT 4,  2,    1 FROM dual UNION ALL
    SELECT 5,11,    1 FROM dual)
SELECT 
   RETAILERID, 
   MAX(PRODUCTID) KEEP (DENSE_RANK LAST ORDER BY PRODUCTTOTAL) AS PRODUCTID, 
   MAX(PRODUCTTOTAL)
FROM t
GROUP BY RETAILERID;


+--------------------------------------+
|RETAILERID|PRODUCTID|MAX(PRODUCTTOTAL)|
+--------------------------------------+
|1         |5        |115              |
|2         |9        |12               |
|3         |3        |3                |
|4         |2        |1                |
|5         |11       |1                |
+--------------------------------------+

Find the row with the highest value in SQL

5 Answers5