Getting the TOP X records per productgroup

Hi experts

We have a product-group table that lists per productgroup the amount of products to be shown. E.g.

ProductGroup NumberOfProducts
Soap 10
Shampoo 20
Toothpaste 15

So now we would like to show the top x products per productgroup ordered by price.

We have tried using SELECT TOP (x) or ROW_NUMBER, but have not yet found a working solution for this.

jonnidip

You can use the ROW_NUMBER() to get the order of the single products into their own group.
I created a test table variable to work with (you can replace it with your table):

declare @testTable as table
(
	ProductGroup varchar(50),
	ProductName varchar(50),
	Price numeric(18,2)
)

insert into @testTable values ('Soap', 'Soap1', 0.01)
insert into @testTable values ('Soap', 'Soap2', 0.02)
insert into @testTable values ('Soap', 'Soap3', 0.03)
insert into @testTable values ('Soap', 'Soap4', 0.04)
insert into @testTable values ('Soap', 'Soap5', 0.05)
insert into @testTable values ('Soap', 'Soap6', 0.06)
insert into @testTable values ('Soap', 'Soap7', 0.07)
insert into @testTable values ('Soap', 'Soap8', 0.08)
insert into @testTable values ('Soap', 'Soap9', 0.09)
insert into @testTable values ('Soap', 'Soap10', 0.1)

insert into @testTable values ('Shampoo', 'Shampoo1', 1.01)
insert into @testTable values ('Shampoo', 'Shampoo2', 1.02)
insert into @testTable values ('Shampoo', 'Shampoo3', 1.03)
insert into @testTable values ('Shampoo', 'Shampoo4', 1.04)
insert into @testTable values ('Shampoo', 'Shampoo5', 1.05)
insert into @testTable values ('Shampoo', 'Shampoo6', 1.06)
insert into @testTable values ('Shampoo', 'Shampoo7', 1.07)
insert into @testTable values ('Shampoo', 'Shampoo8', 1.08)
insert into @testTable values ('Shampoo', 'Shampoo9', 1.09)
insert into @testTable values ('Shampoo', 'Shampoo10', 1.10)
insert into @testTable values ('Shampoo', 'Shampoo11', 1.11)
insert into @testTable values ('Shampoo', 'Shampoo12', 1.12)
insert into @testTable values ('Shampoo', 'Shampoo13', 1.13)
insert into @testTable values ('Shampoo', 'Shampoo14', 1.14)
insert into @testTable values ('Shampoo', 'Shampoo15', 1.15)
insert into @testTable values ('Shampoo', 'Shampoo16', 1.16)
insert into @testTable values ('Shampoo', 'Shampoo17', 1.17)
insert into @testTable values ('Shampoo', 'Shampoo18', 1.18)
insert into @testTable values ('Shampoo', 'Shampoo19', 1.19)
insert into @testTable values ('Shampoo', 'Shampoo20', 1.2)

insert into @testTable values ('Toothpaste', 'Toothpaste1', 2.01)
insert into @testTable values ('Toothpaste', 'Toothpaste2', 2.02)
insert into @testTable values ('Toothpaste', 'Toothpaste3', 2.03)
insert into @testTable values ('Toothpaste', 'Toothpaste4', 2.04)
insert into @testTable values ('Toothpaste', 'Toothpaste5', 2.05)
insert into @testTable values ('Toothpaste', 'Toothpaste6', 2.06)
insert into @testTable values ('Toothpaste', 'Toothpaste7', 2.07)
insert into @testTable values ('Toothpaste', 'Toothpaste8', 2.08)
insert into @testTable values ('Toothpaste', 'Toothpaste9', 2.09)
insert into @testTable values ('Toothpaste', 'Toothpaste10', 2.1)
insert into @testTable values ('Toothpaste', 'Toothpaste11', 2.11)
insert into @testTable values ('Toothpaste', 'Toothpaste12', 2.12)
insert into @testTable values ('Toothpaste', 'Toothpaste13', 2.13)
insert into @testTable values ('Toothpaste', 'Toothpaste14', 2.14)
insert into @testTable values ('Toothpaste', 'Toothpaste15', 2.15)

select ProductGroup, COUNT(*) as NumberOfProducts
from @testTable
group by ProductGroup

Open in new window

This will output as your example.
Then you may "partition" your query by "ProductGroup" and order the rows by Price:

select *
from
	(select ProductGroup, ProductName, Price,
			row_number() over(partition by ProductGroup order by Price) as RowNum
	from @testTable) s
where RowNum <= 3

Open in new window

This will output:

ProductGroup      ProductName     Price       RowNum
Shampoo           Shampoo1        1.01        1
Shampoo           Shampoo2        1.02        2
Shampoo           Shampoo3        1.03        3
Soap              Soap1           0.01        1
Soap              Soap2           0.02        2
Soap              Soap3           0.03        3
Toothpaste        Toothpaste1     2.01        1
Toothpaste        Toothpaste2     2.02        2
Toothpaste        Toothpaste3     2.03        3

Open in new window

Regards.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

$Avatar of riffrack$

riffrack

ASKER

Absolutely perfect, this solution works for my tables containg more products than are to be shown.