CODEWITHSUNDEEP
mysqlgroup-by
AlinkaShil
AlinkaShil

Reputation: 21

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

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?

Upvotes: 0

Views: 1523

Answers (2)

Geepy
Geepy

Reputation: 62

If you want to display the 5 orders with the most items, you could use this:

SELECT TOP 5
  o.orderID, count(*)
FROM Order o INNER JOIN OrderItem oi on o.OrderID = oi.OrderID
GROUP BY o.OrderID
ORDER BY count(*) DESC

For displaying the top 5 items in each order, it is a bit more complex, because you cannot use windowed functions in WHERE or ORDER BY. For this, you need a subquery to retrieve the ranking and sort/filter the result.

SELECT * 
FROM 
(
  SELECT 
    oi.*,
    ROW_NUMBER() OVER (PARTITION BY oi.OrderID ORDER BY oi.Something) as ranking
  FROM OrderItem oi
) tmp WHERE ranking < 6
ORDER BY tmp.OrderID, tmp.ranking

If that is not your intent, please give an example for your intended result.

Upvotes: 0

Vipul
Vipul

Reputation: 836

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;

Upvotes: 2

Related Questions