assign a new number to a different category

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

General SQL Server Forums

New to SQL Server Programming

assign a new number to a different category

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-09-12 : 03:52:39

hi there,

I have a query that returns the following and I need to workout how to return the 'sequence_no'. The sequence_no will grow by 1 everytime the sequence_item and/or store changes.
Eg.

select sequence, store, price, ?? from data

secquence_item, price, store, sequence_no
adaa, a, $12, ? (should be 1)
adaa, a, $16, ? (should be 1)
adaa, a, $15, ? (should be 1)
addd, a, $14, ? (should be 2)
addd, a, $14, ? (should be 2)
adee, a, $15, ? (should be 3)
adee, a, $14, ? (should be 3)
adee, a, $19, ? (should be 3)
adaa, b, $12, ? (should be 4)
adaa, b, $16, ? (should be 4)
adaa, b, $15, ? (should be 4)

there are over 50 different sequence items and over 20 stores. So it is too difficult to calculate manually. I want the sequnce number to grow by one everytime there is a change in sequence_item and or store.

The results are order by sequence_item and store asc.

No idea where to start on this one or even what to search

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-09-12 : 04:30:05

Something like this maybe?


SELECT 
	  data.store
	, data.secquence_item
	, data.price
	, GetSequence.sequence_no 
FROM 
	data
INNER JOIN
	(
		SELECT 
			  ROW_NUMBER() OVER (order by  secquence_item, store) AS sequence_no 
			, store 
			, secquence_item
		FROM 
			(
				SELECT DISTINCT store, secquence_item FROM data 
			) SeqNum
	) GetSequence
ON
	GetSequence.store = data.store
AND
	GetSequence.secquence_item = data.secquence_item

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-09-12 : 05:16:23

[code]declare @data table(secquence_item varchar(4), store varchar(1), price numeric(2))

insert into @data select
'adaa', 'a', 12 union all select--, ? (should be 1)
'adaa', 'a', 16 union all select--, ? (should be 1)
'adaa', 'a', 15 union all select--, ? (should be 1)
'addd', 'a', 14 union all select--, ? (should be 2)
'addd', 'a', 14 union all select--, ? (should be 2)
'adee', 'a', 15 union all select--, ? (should be 3)
'adee', 'a', 14 union all select--, ? (should be 3)
'adee', 'a', 19 union all select--, ? (should be 3)
'adaa', 'b', 12 union all select--, ? (should be 4)
'adaa', 'b', 16 union all select--, ? (should be 4)
'adaa', 'b', 15--, ? (should be 4)

select *, dense_rank() over (order by store+secquence_item)
from @data[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:09:16

to be safe





declare @data table(secquence_item varchar(4), store varchar(5), price numeric(2))

insert into @data select
'adaa', 'a', 12 union all select--, ? (should be 1)
'adaa', 'a', 16 union all select--, ? (should be 1)
'adaa', 'a', 15 union all select--, ? (should be 1)
'addd', 'a', 14 union all select--, ? (should be 2)
'addd', 'a', 14 union all select--, ? (should be 2)
'adee', 'a', 15 union all select--, ? (should be 3)
'adee', 'a', 14 union all select--, ? (should be 3)
'adee', 'a', 19 union all select--, ? (should be 3)
'adaa', 'b', 12 union all select--, ? (should be 4)
'adaa', 'b', 16 union all select--, ? (should be 4)
'adaa', 'b', 15 union all select --? (should be 4)
'daa', 'aa', 12 union all select--, ? (should be 1)
'daa', 'aa', 16 union all select--, ? (should be 1)
'daa', 'aa', 15 --, ? (should be 1)

--waterduck soln
select *, dense_rank() over (order by store+secquence_item)
from @data

--visakh modified soln
select *, dense_rank() over (order by store,secquence_item)
from @data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources