displaying range prices by product

Question

Post reply

displaying range prices by product

mdspham

Grasshopper

Points: 10
More actions
January 14, 2011 at 10:01 pm

#230403

I have a table with these columns:
price product
1 toy
2 toy
3 toy
4 toy
5 game
6 game
7 game
8 game
9 toy
10 toy
11 game
I would like to put together a query that would display
1 - 4 toy
5 - 8 game
9 - 10 toy
11 game
thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

How's this?

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (price int, product varchar(10));

INSERT INTO @test-2 (price, product)

SELECT 1, 'toy' UNION ALL

SELECT 2, 'toy' UNION ALL

SELECT 3, 'toy' UNION ALL

SELECT 4, 'toy' UNION ALL

SELECT 5, 'game' UNION ALL

SELECT 6, 'game' UNION ALL

SELECT 7, 'game' UNION ALL

SELECT 8, 'game' UNION ALL

SELECT 9, 'toy' UNION ALL

SELECT 10, 'toy' UNION ALL

SELECT 11, 'game';

WITH CTE AS

(

SELECT product, price,

Grp = ROW_NUMBER() OVER (ORDER BY price) -

ROW_NUMBER() OVER (PARTITION BY product ORDER BY price)

FROM @test-2

)

SELECT product, Starts = min(price), Ends = max(price)

FROM CTE

GROUP BY product, Grp

ORDER BY Starts;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mdspham Grasshopper Points: 10 More actions · Answer 2

mdspham

Grasshopper

Points: 10

January 17, 2011 at 9:53 pm

#1274469

thanks Wayne! it worked like a charm.

kondapaturi.srinivasarao SSC Rookie Points: 29 More actions · Answer 3

declare @products table

(

price int identity(1,1) primary key,

product varchar(30)

);

INSERT into @products

Select 'toy' union all

Select 'game' union all

Select 'toy' union all

Select 'game'

;with cte

as

(

select product,price,

grp=row_number() over(order by price)

-row_number() over(PARTITION BY product order by product) from @products

)

--select * from cte

SELECT convert(varchar(1000),min(price))+'-'+convert(varchar(1000),max(price)),product

FROM CTE

GROUP BY product, Grp

ORDER BY min(price);