Xtykutl

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.

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

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

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;

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                |

+--------------------------------------+

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;