how to add sequential number - SQL Server Forums

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)

how to add sequential number

Author

Topic

donar
Starting Member

22 Posts

Posted - 2004-10-07 : 10:51:03

I have a table: ID(int), Value(Varchar)
it looks like this:
ID Value
----------
4 spring
4 winter
4 summber
11 east
11 west
13 east
14 north

I want the table add one field, and generate sequential number for it,like this
Seq ID Value
-----------------
1 4 spring
2 4 winter
3 4 summber
1 11 east
2 11 west
1 13 east
1 14 north

How can I do that? I can generat sequential number for one ID ,how can I do for all different ID?

Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 10:53:44

Is ordering important for the <value> field ?
Is <value> unique for each id?

CREATE TABLE T (ID INT, Value VARCHAR(30) )	
----------
INSERT INTO T 
SELECT 4, 'spring' UNION ALL
SELECT 4, 'winter' UNION ALL
SELECT 4, 'summber' UNION ALL
SELECT 11, 'east' UNION ALL
SELECT 11, 'west' UNION ALL
SELECT 13, 'east' UNION ALL
SELECT 14, 'north'

select n seq,id,value
from
(
	select top 100 percent t1.id,t1.value,(select count(1) from t t2 where id = t1.id and t1.value <= t2.value) n
	from t t1
	order by t1.id,t1.value
) d
order by id,n,value
drop table t

donar
Starting Member

22 Posts

Posted - 2004-10-07 : 13:16:30

Thanks a lot!
It really works! And it is exactly what I want.

But I have one question: for the third select, select count(1) from t t2 where id = t1.id
why use id = t1.id?

How it works?

Thanks!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 15:23:08

Here is a very good article describing this technique. Have a look.

http://www.sqlteam.com/item.asp?ItemID=12654

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-08 : 20:05:35

The technique in the article is best applied to generating the sequences on the fly.
If you actually have a column in the table that you need to update (or want to add one), a more efficient way to do it would be;

alter table t add seq int

declare @seq int, @id int
set @seq = 1

update T
set seq = @seq,
@seq = case when @id = id then @seq + 1 else 1 end,
@id = id

That depends on there being a clustered index on id+value.
(i.e. create clustered index t_id_val on t (id,value) )

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources