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

Try this

SELECT DISTINCT 
        A.Product_Group_code
        ,B.Sub_Product_Group_code
        ,A.Product_code
        ,A.Product_name
FROM dbo.A A
    LEFT JOIN dbo.B B
        ON A.Product_code = B.Product_code
WHERE  B.Product_code IN
                (
                    SELECT TOP 5 E.Product_code
                    FROM dbo.A D
                        LEFT JOIN dbo.B E
                            ON D.Product_code = E.Product_code
                    WHERE E.Sub_Product_Group_code = B.Sub_Product_Group_code
                )
        AND B.Sub_Product_Group_code IS NOT NULL
ORDER BY B.Sub_Product_Group_code,A.Product_name

Answer 2

Here is a great solution I just found.

Select the TOP n Rows For Each Group Arnie Rowland, March 13, 2008

There are Multiple Rows for Each Category, and there is a desire to SELECT ONLY the TOP two (2) Rows per Category by Price. For example, from the following data:

RowID    Category    ID  Description     Price
1    Pot     A1  Small Saucepan  21.50
2    Pot     A2  1 Qt Saucepan   29.95
3    Pot     A3  1.5 Qt Saucepan     33.95
4    Pot     A4  Double Boiler   39.50
5    Pot     A5  Stewpot     49.50
6    Pot     A6  Pressure Cooker     79.95
7    Pan     B1  8" Pie  6.95
8    Pan     B2  8" Sq Cake  7.50
9    Pan     B3  Bundt Cake  12.50
10   Pan     B4  9x12 Brownie    7.95
11   Bowl    C1  Lg Mixing   27.50
12   Bowl    C2  Sm Mixing   17.50
13   Tools   T1  14" Spatula     9.95

The desired output is:

RowID    Category    ID  Description     Price
11   Bowl    C1  Lg Mixing   27.50
12   Bowl    C2  Sm Mixing   17.50
9    Pan     B3  Bundt Cake  12.50
10   Pan     B4  9x12 Brownie    7.95
6    Pot     A6  Pressure Cooker     79.95
5    Pot     A5  Stewpot     49.50
13   Tools   T1  14" Spatula     9.95

There are several methods to accomplish the desired output. This demonstration provides a Solution for SQL Server 2005 / SQL Server 2008 , and then a Solution for SQL Server 2000.

Create Sample Data for Both Solutions

-- Suppress data loading messages
SET NOCOUNT ON

-- Create Sample Data using a Table Varable
DECLARE @MyTable table
   (  RowID         int   IDENTITY, 
      Category      varchar(5),
      [ID]          varchar(5),
      [Description] varchar(25),
      Price         decimal(10,2)
   )

-- Load Sample Data

INSERT INTO @MyTable VALUES ( 'Pot', 'A1', 'Small Saucepan', 21.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A2', '1 Qt Saucepan', 29.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A3', '1.5 Qt Saucepan', 33.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A4', 'Double Boiler', 39.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A5', 'Stewpot', 49.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A6', 'Pressure Cooker', 79.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B1', '8"" Pie', 6.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B2', '8"" Sq Cake', 7.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B3', 'Bundt Cake', 12.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B4', '9x12 Brownie', 7.95 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C1', 'Lg Mixing', 27.50 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C2', 'Sm Mixing', 17.50 )
INSERT INTO @MyTable VALUES ( 'Tools', 'T1', '14"" Spatula', 9.95 )
Return to Top

SQL Server 2005 / SQL Server 2008 Solution

--Query to Retrieve Desired Data
SELECT
   RowID,
   Category,
   [ID],
   [Description],
   Price
FROM (SELECT
         ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
         RowID,
         Category,
         [ID],
         [Description],
         Price
      FROM @MyTable
      ) dt
WHERE RowNumber <= 2

-- Results
RowID Category  ID Description     Price
11    Bowl      C1 Lg Mixing       27.50
12    Bowl      C2 Sm Mixing       17.50
9     Pan       B3 Bundt Cake      12.50
10    Pan       B4 9x12 Brownie    7.95
6     Pot       A6 Pressure Cooker 79.95
5     Pot       A5 Stewpot         49.50
13    Tools     T1 14" Spatula     9.95
Return to Top

SQL Server 2005 / SQL Server 2008 Solution using a CTE (Added by: Jacob Sebastian)

-- Define a CTE with the name "dt" 
;WITH dt AS (
     SELECT
         ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
         RowID,
         Category,
         [ID],
         [Description],
         Price
      FROM @MyTable
)
-- and select the data from the CTE
SELECT
   RowID,
   Category,
   [ID],
   [Description],
   Price
FROM dt
WHERE RowNumber <= 2

-- Results
RowID Category  ID Description     Price
11    Bowl      C1 Lg Mixing       27.50
12    Bowl      C2 Sm Mixing       17.50
9     Pan       B3 Bundt Cake      12.50
10    Pan       B4 9x12 Brownie    7.95
6     Pot       A6 Pressure Cooker 79.95
5     Pot       A5 Stewpot         49.50
13    Tools     T1 14" Spatula     9.95
Return to Top

SQL 2000 Solution

--Query to Retrieve Desired Data
SELECT DISTINCT
   RowID,
   Category,
   [ID],
   [Description],
   Price
FROM @MyTable t1
WHERE RowID IN (SELECT TOP 2
                   RowID
                FROM @MyTable t2
                WHERE t2.Category = t1.Category
                ORDER BY Price DESC
               )
ORDER BY 
   Category,
   Price DESC

-- Results
RowID Category  ID Description     Price
11    Bowl      C1 Lg Mixing       27.50
12    Bowl      C2 Sm Mixing       17.50
9     Pan       B3 Bundt Cake      12.50
10    Pan       B4 9x12 Brownie    7.95
6     Pot       A6 Pressure Cooker 79.95
5     Pot       A5 Stewpot         49.50
13    Tools     T1 14" Spatula     9.95

From: Select the TOP n Rows For Each Group

Answer 3

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

Answer 4

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