How to retrieve top N rows from each group in SQL with JOIN tables

February 3, 2023

I need a database with two tabels. I need to JOIN them, group the records and then display top 5 rows from each group. Here is my initial query without top N records:

SELECT customerId, itemId, count(itemId) as num FROM Orders JOIN OrderItems ON orderId=orderId ORDER BY num DESC GROUP BY customerId

I suppose I would need a ROWNUM and PARTITION BY here, but I have no idea how to combine them with JOIN tables. Could you please help me?

>Solution :

To retrieve the top 5 rows per group in your query, you can use a subquery with the ROW_NUMBER function and a PARTITION BY clause.

  SELECT customerId, itemId, num
    FROM (
      SELECT customerId, itemId, num,
             ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY num DESC) as rn
      FROM (
        SELECT customerId, itemId, count(itemId) as num
        FROM Orders
        JOIN OrderItems ON Orders.orderId = OrderItems.orderId
        GROUP BY customerId, itemId
      ) as subquery1
    ) as subquery2
    WHERE rn <= 5;