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).
Selecting a top n records for each category from any table, can be done easily using row_number function which generates a sequential integer to each row within a partition of a result set.
Example - Using LIMIT keywordSELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id DESC LIMIT 5; This SQL SELECT LIMIT example would select the first 5 records from the contacts table where the website is 'TechOnTheNet.com'.
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
???
) X
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With