Question 20958 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search