Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - SELECT TOP 5 rows for each FK

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).

like image 534
db1234 Avatar asked Sep 20 '09 08:09

db1234


People also ask

How do you select Top 10 records from each category in SQL?

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.

How do I limit the first 5 rows in SQL?

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'.


2 Answers

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
like image 73
gbn Avatar answered Nov 01 '22 23:11

gbn


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
like image 45
Blorgbeard Avatar answered Nov 02 '22 00:11

Blorgbeard