SQL Server - SELECT TOP 5 rows for each FK

Question

I have the following query that scans the top 5 products matching the search. Each product is associated with a store.

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

I need to expand this so that he returns me the TOP 5 products in each store.

Can someone tell me how the query could be changed to achieve this? - i.e. select the TOP-5 products matching "% Christmas%" in each store (and not in the current one, which shows the TOP-5 products matching "% chrismas%" in all stores).

Answer 1

ORDER BY, TOP ROW_NUMBER, 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 2

:

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

Answer 3

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 4

, .

TOP-n , 13 2008 .

, (2) . , :

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

:

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

. SQL Server 2005/SQL Server 2008, SQL Server 2000.

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

--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 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 TOP n Rows for each group.