Get the first 10 rows for each group

Question

I have three tables:

Customer(IdCustomer, Name)
Product(IdProduct, Product)
Order(IdProduct, IdCustomer, nbOrders)

So the Order table stores how many times a customer has ordered a product.

I need a view like this:

TopOrder(Name, Product, nbCommands)

But I only want 10 products for each customer, the ones he ordered the most and I can't figure it out.

Answer 1

The dense_rank window function should be exactly what the doctor prescribed:

CREATE View TopOrder AS
SELECT Name, Product, nbOrders
FROM   (SELECT Name, Product, nbOrders,
               DENSE_RANK() OVER (PARTITION BY o.idCustomer
                                  ORDER BY nbOrders DESC) AS rk
        FROM   Customer c
        JOIN   Orders o ON c.idCustomer = o.idCustomer
        JOIN   Product p ON p.idProduct = o.idProduct
       ) t
WHERE  rk <= 10