Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Top N of each group multiple group by

Author  Topic 

GSWilliams1906
Starting Member

4 Posts
Posted - 2006-08-31 : 09:08:24
I am not sure if I should start a new thread or latch on to this one, but here it goes. I have a similar problem:

I have the following tables. First is a pricing table:

Terminal Supplier Product Day Price
Terminal 1 Supplier 1 Product 1 Day 1 2.30
Terminal 1 Supplier 2 Product 1 Day 1 2.35
Terminal 1 Supplier 3 Product 1 Day 1 3.00
Terminal 1 Supplier 4 Product 1 Day 1 2.78
Terminal 1 Supplier 5 Product 1 Day 1 2.64
Terminal 2 Supplier 1 Product 1 Day 1 2.30
Terminal 2 Supplier 2 Product 1 Day 1 2.35
Terminal 2 Supplier 3 Product 1 Day 1 2.58
Terminal 2 Supplier 4 Product 1 Day 1 2.58
Terminal 2 Supplier 5 Product 1 Day 1 2.65
Terminal 3 Supplier 1 Product 1 Day 1 2.30
Terminal 3 Supplier 2 Product 1 Day 1 2.35
Terminal 3 Supplier 3 Product 1 Day 1 3.00
Terminal 3 Supplier 4 Product 1 Day 1 2.78
Terminal 3 Supplier 6 Product 1 Day 1 2.59
Terminal 4 Supplier 1 Product 1 Day 1 2.30
Terminal 4 Supplier 2 Product 1 Day 1 2.29
Terminal 4 Supplier 3 Product 1 Day 1 3.01
Terminal 4 Supplier 5 Product 1 Day 1 2.64
Terminal 4 Supplier 7 Product 1 Day 1 2.45
Terminal 5 Supplier 1 Product 1 Day 1 2.30
Terminal 6 Supplier 2 Product 1 Day 1 2.35
Terminal 6 Supplier 3 Product 1 Day 1 3.00
Terminal 6 Supplier 6 Product 1 Day 1 2.61
Terminal 6 Supplier 7 Product 1 Day 1 2.54
Terminal 1 Supplier 1 Product 1 Day 2 2.31
Terminal 1 Supplier 2 Product 1 Day 2 2.36
Terminal 1 Supplier 3 Product 1 Day 2 3.00
Terminal 1 Supplier 4 Product 1 Day 2 2.78
Terminal 1 Supplier 5 Product 1 Day 2 2.65
Terminal 2 Supplier 1 Product 1 Day 2 2.31
Terminal 2 Supplier 2 Product 1 Day 2 2.37
Terminal 2 Supplier 3 Product 1 Day 2 2.58
Terminal 2 Supplier 4 Product 1 Day 2 2.78
Terminal 2 Supplier 5 Product 1 Day 2 2.64
Terminal 3 Supplier 1 Product 1 Day 2 2.31
Terminal 3 Supplier 2 Product 1 Day 2 2.36
Terminal 3 Supplier 3 Product 1 Day 2 3.00
Terminal 3 Supplier 4 Product 1 Day 2 2.78
Terminal 3 Supplier 6 Product 1 Day 2 2.60
Terminal 4 Supplier 1 Product 1 Day 2 2.31
Terminal 4 Supplier 2 Product 1 Day 2 2.36
Terminal 4 Supplier 3 Product 1 Day 2 3.01
Terminal 4 Supplier 5 Product 1 Day 2 2.64
Terminal 4 Supplier 7 Product 1 Day 2 2.48
Terminal 5 Supplier 1 Product 1 Day 2 2.31
Terminal 6 Supplier 2 Product 1 Day 2 2.36
Terminal 6 Supplier 3 Product 1 Day 2 3.00
Terminal 6 Supplier 6 Product 1 Day 2 2.61
Terminal 6 Supplier 7 Product 1 Day 2 2.73


I have a customer who want to receive the lowest price for the given product for each terminal.

Terminal Supplier Product Day Price
Terminal 1 Supplier 1 Product 1 Day 1 2.30
Terminal 1 Supplier 2 Product 1 Day 1 2.35
Terminal 1 Supplier 5 Product 1 Day 1 2.64
Terminal 2 Supplier 1 Product 1 Day 1 2.30
Terminal 2 Supplier 2 Product 1 Day 1 2.35
Terminal 2 Supplier 3 Product 1 Day 1 2.58
Terminal 2 Supplier 4 Product 1 Day 1 2.58
Terminal 3 Supplier 1 Product 1 Day 1 2.30
Terminal 3 Supplier 2 Product 1 Day 1 2.35
Terminal 3 Supplier 6 Product 1 Day 1 2.59
Terminal 4 Supplier 2 Product 1 Day 1 2.29
Terminal 4 Supplier 1 Product 1 Day 1 2.30
Terminal 4 Supplier 7 Product 1 Day 1 2.45
Terminal 5 Supplier 1 Product 1 Day 1 2.30
Terminal 6 Supplier 2 Product 1 Day 1 2.35
Terminal 6 Supplier 7 Product 1 Day 1 2.54
Terminal 6 Supplier 6 Product 1 Day 1 2.61


Now in the pricing table there are other products. When I do a Top N where in equal 3. I only get back three prices for all terminals.
When I do a select * from customer template WHERE (Top 3 Select * from pricing table where terminal = customer template terminal ) I get back the three prices, but multiple products. I only want 1 product. When I add in the and where product = product 1 I get an empty dataset.

I know the query I have above are not the right syntax, it just to give you an idea of what I’m trying to do. I want the lowest N price for product 1 from each terminal based upon the customers template who one customer may want all terminals or another customer may want terminal 1,2, and 5 only. Can anyone help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-31 : 09:28:50
Something like this?
declare @test table (Terminal varchar(20), supplier varchar(20), product varchar(20), day varchar(20), Price smallmoney)

insert	@test
select	'Terminal 1', 'supplier 1', 'product 1', 'day 1', 2.30 union all
select	'Terminal 1', 'supplier 2', 'product 1', 'day 1', 2.35 union all
select	'Terminal 1', 'supplier 3', 'product 1', 'day 1', 3.00 union all
select	'Terminal 1', 'supplier 4', 'product 1', 'day 1', 2.78 union all
select	'Terminal 1', 'supplier 5', 'product 1', 'day 1', 2.64 union all
select	'Terminal 2', 'supplier 1', 'product 1', 'day 1', 2.30 union all
select	'Terminal 2', 'supplier 2', 'product 1', 'day 1', 2.35 union all
select	'Terminal 2', 'supplier 3', 'product 1', 'day 1', 2.58 union all
select	'Terminal 2', 'supplier 4', 'product 1', 'day 1', 2.58 union all
select	'Terminal 2', 'supplier 5', 'product 1', 'day 1', 2.65 union all
select	'Terminal 3', 'supplier 1', 'product 1', 'day 1', 2.30 union all
select	'Terminal 3', 'supplier 2', 'product 1', 'day 1', 2.35 union all
select	'Terminal 3', 'supplier 3', 'product 1', 'day 1', 3.00 union all
select	'Terminal 3', 'supplier 4', 'product 1', 'day 1', 2.78 union all
select	'Terminal 3', 'supplier 6', 'product 1', 'day 1', 2.59 union all
select	'Terminal 4', 'supplier 1', 'product 1', 'day 1', 2.30 union all
select	'Terminal 4', 'supplier 2', 'product 1', 'day 1', 2.29 union all
select	'Terminal 4', 'supplier 3', 'product 1', 'day 1', 3.01 union all
select	'Terminal 4', 'supplier 5', 'product 1', 'day 1', 2.64 union all
select	'Terminal 4', 'supplier 7', 'product 1', 'day 1', 2.45 union all
select	'Terminal 5', 'supplier 1', 'product 1', 'day 1', 2.30 union all
select	'Terminal 6', 'supplier 2', 'product 1', 'day 1', 2.35 union all
select	'Terminal 6', 'supplier 3', 'product 1', 'day 1', 3.00 union all
select	'Terminal 6', 'supplier 6', 'product 1', 'day 1', 2.61 union all
select	'Terminal 6', 'supplier 7', 'product 1', 'day 1', 2.54 union all
select	'Terminal 1', 'supplier 1', 'product 1', 'day 2', 2.31 union all
select	'Terminal 1', 'supplier 2', 'product 1', 'day 2', 2.36 union all
select	'Terminal 1', 'supplier 3', 'product 1', 'day 2', 3.00 union all
select	'Terminal 1', 'supplier 4', 'product 1', 'day 2', 2.78 union all
select	'Terminal 1', 'supplier 5', 'product 1', 'day 2', 2.65 union all
select	'Terminal 2', 'supplier 1', 'product 1', 'day 2', 2.31 union all
select	'Terminal 2', 'supplier 2', 'product 1', 'day 2', 2.37 union all
select	'Terminal 2', 'supplier 3', 'product 1', 'day 2', 2.58 union all
select	'Terminal 2', 'supplier 4', 'product 1', 'day 2', 2.78 union all
select	'Terminal 2', 'supplier 5', 'product 1', 'day 2', 2.64 union all
select	'Terminal 3', 'supplier 1', 'product 1', 'day 2', 2.31 union all
select	'Terminal 3', 'supplier 2', 'product 1', 'day 2', 2.36 union all
select	'Terminal 3', 'supplier 3', 'product 1', 'day 2', 3.00 union all
select	'Terminal 3', 'supplier 4', 'product 1', 'day 2', 2.78 union all
select	'Terminal 3', 'supplier 6', 'product 1', 'day 2', 2.60 union all
select	'Terminal 4', 'supplier 1', 'product 1', 'day 2', 2.31 union all
select	'Terminal 4', 'supplier 2', 'product 1', 'day 2', 2.36 union all
select	'Terminal 4', 'supplier 3', 'product 1', 'day 2', 3.01 union all
select	'Terminal 4', 'supplier 5', 'product 1', 'day 2', 2.64 union all
select	'Terminal 4', 'supplier 7', 'product 1', 'day 2', 2.48 union all
select	'Terminal 5', 'supplier 1', 'product 1', 'day 2', 2.31 union all
select	'Terminal 6', 'supplier 2', 'product 1', 'day 2', 2.36 union all
select	'Terminal 6', 'supplier 3', 'product 1', 'day 2', 3.00 union all
select	'Terminal 6', 'supplier 6', 'product 1', 'day 2', 2.61 union all
select	'Terminal 6', 'supplier 7', 'product 1', 'day 2', 2.73


SELECT		a.Terminal,
		a.Price
FROM		@Test a
INNER JOIN	@Test b ON b.Price <= a.Price AND b.Terminal = a.Terminal
GROUP BY	a.Terminal,
		a.Price
HAVING		COUNT(DISTINCT b.Price) <= 3
ORDER BY	a.Terminal,
		a.Price

SELECT		a.Terminal,
		a.Price
FROM		@Test a
WHERE		(SELECT COUNT(DISTINCT b.Price) FROM @Test b WHERE b.Price <= a.Price AND b.Terminal = a.Terminal) <= 3
ORDER BY	a.Terminal,
		a.Price

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site