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