Given the following Products table:
As you can see:
- each row represents one product.
- The table has three columns: ProductID, ProductName and ProuctPrice, which contain information about each product.
- The ProductPrice values range between 10 and 300.
Based on the price range, we can say that there are three product groups : those with a price range of 1-100, those with a price range of 101-200, and those with a price range of 201-300.
Write a SELECT statement that retrieves the lowest two prices for each group.
If you want to take this challenge one step forward:
The script below used a series of simple INSERT statements to populate Products table.
Use a TSQL loop to achieve the same goal. Then, try using recursive SELECT statement.
DROP TABLE products
GO
CREATE TABLE Products (productID int, ProductName varchar(25), ProductPrice int)
GO
INSERT INTO products VALUES (1, 'Product1' , 10),
(2, 'Product2' , 20),
(3, 'Product3' , 30),
(4, 'Product4' , 40),
(5, 'Product5' , 50),
(6, 'Product6' , 60),
(7, 'Product7' , 70),
(8, 'Product8' , 80),
(9, 'Product9' , 90),
(10, 'Product10' , 100),
(11, 'Product11' , 110),
(12, 'Product12' , 120),
(13, 'Product13' , 130),
(14, 'Product14' , 140),
(15, 'Product15' , 150),
(16, 'Product16' , 160),
(17, 'Product17' , 170),
(18, 'Product18' , 180),
(19, 'Product19' , 190),
(20, 'Product20' , 200),
(21, 'Product21' , 210),
(22, 'Product22' , 220),
(23, 'Product23' , 230),
(24, 'Product24' , 240),
(25, 'Product25' , 250),
(26, 'Product26' , 260),
(27, 'Product27' , 270),
(28, 'Product28' , 280),
(29, 'Product29' , 290),
(30, 'Product30' , 300)
-- 1. retrieving the lowest two prices for each group.
SELECT productID,ProductName,ProductPrice FROM
(
SELECT productID,ProductName,ProductPrice,ROW_NUMBER() OVER
(
PARTITION BY
CASE WHEN ProductPrice BETWEEN 1 AND 100 THEN 1
WHEN ProductPrice BETWEEN 101 AND 200 THEN 2
WHEN ProductPrice BETWEEN 201 AND 300 THEN 3
ELSE 4
END ORDER BY (ProductPrice)
) AS RNK
FROM products
) RANKED_TBL
WHERE RANKED_TBL.RNK <= 2
-- 2. Populate the table using a TSQL loop
DECLARE
@counter int
BEGIN
SET @counter = 1
WHILE @counter <= 30
BEGIN
INSERT INTO Products VALUES (@counter, 'Products' + CAST(@counter AS VARCHAR), @counter * 10)
SET @counter = @counter + 1
END
END
-- 3. Populate the table using a Recursive CTE
WITH "ProductsCTE" (id,name,price)
AS
(
SELECT 1, CAST('Product1' AS VARCHAR(MAX)), CAST(10 AS INT)
UNION ALL
SELECT id+1 , CAST('Product' + CAST(id+1 AS varchar) AS VARCHAR(MAX)), CAST(PRICE+10 AS INT) FROM "ProductsCTE"
WHERE id < 30
)
INSERT INTO products
SELECT * FROM "ProductsCTE"