Find the row with the highest value in SQL

438 views sql
6

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.

answered question

2 Answers

1

You can use the below query to find the product with the highest number of orders

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

posted this
4

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

posted this

Have an answer?

JD

Please login first before posting an answer.