Fetching Rows by Groups

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.

You can use this script to create and populate the table


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)

Click here to see the solution


-- 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 &lt;= 2 

-- 2. Populate the table using a TSQL loop
DECLARE 
 @counter int 
BEGIN 
 SET @counter = 1
 WHILE @counter &lt;= 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 &lt; 30 
 ) 
INSERT INTO products 
SELECT * FROM "ProductsCTE"

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…

About Us

UpScale Analytics specializes in training solutions for organizations and individuals in the fields of data.

UpScale Analytics is also one of the largest platforms in the world for learning SQL by doing.

Fetching Rows by Groups

About Us

Popular Courses

Desicion Making with Basic SQL

PL/SQL Programming

Advanced Power BI

Quick Links