SQL Server - SELECT TOP 5 rows for each FK

Question

I've got the following query, that looks up the TOP 5 Products matching the search. Each Product is associated with a Shop

SELECT TOP 5 * FROM Products p, Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'

I need to extend this so that it returns me the TOP 5 Products in each Shop.

Could anyone let me know how the query could be modified to achieve this? - i.e. choose the TOP 5 products matching "%christmas%" in each shop (rather than the current which shows the TOP 5 products matching "%chrismas%" across all shops).

Answer 1

You're actually missing an ORDER BY to make the TOP meaningful, or any solution based on ROW_NUMBER which requires an ORDER BY.

SELECT
    *
FROM
    Shops s 
    CROSS APPLY
    (SELECT TOP 5
        *
    FROM
        Products p
    WHERE
        p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%')
ORDER BY --added on edit
    ???

Answer 2

Try this:

select * from (
    select *, rn = row_number() over (partition by s.ShopId order by p.ProductName)
    from Products p, Shops s 
    where p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'
) a where a.rn <= 5