Adding a sequencial number to a table

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

Transact-SQL (2000)

Adding a sequencial number to a table

Author

Topic

markusf
Starting Member

15 Posts

Posted - 2006-11-24 : 03:48:13

I have a result-table where many clients writes it denormalized result to. I want to add a sequence number to each of these results. The result is from a temp table:
INSERT INTO result
SELECT * FROM #tmp_result

I want the result-table to look like this
ID ClientHash SeqNumber Field1 Field2
1 xxx 1 a b
2 xxx 2 aa bb
3 xxx 3 aaa bbb
4 yyy 1 c c
5 yyy 2 d d
6 zzz 1 e e

Is there a easy way to do this in SQLServer 2000 ?

--
Markus Foss, Norway

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 03:52:21

Why not add IDENTITY column to the result table?

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-24 : 04:06:05

Where do you want to show data?
If you use reports you can make use of Record number and reset to each group

Madhivanan

Failing to plan is Planning to fail

markusf
Starting Member

15 Posts

Posted - 2006-11-24 : 04:29:32

@harsh_athalye: there can be simoultanois inserts to the table, and I want all sequences to start at 1 per search (each user gets his unique ClientHash, used to lookup the result)

@madhivanan: This is part of a internal search invoked over a webservice, but since the results are potentially huge (50 000+ results), i do _not_ want to serialize them to XML and send them over the network. So the client will just get the ClientHash in return, and can then use the result as he wants. By providing a sequence number going from 1-[num result], it will be very easy to ie. enable Paging.

--
Markus Foss, Norway

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 04:37:24

If the results in the temporary table is sorted with client hash and ID column is identity, use this

-- prepare test data
declare @tmp table (ID int, ClientHash varchar(3), SeqNumber int, Field1 varchar(3), Field2 varchar(3))

insert	@tmp
select	1, 'xxx', null, 'a', 'b' union all
select	2, 'xxx', null, 'aa', 'bb' union all
select	3, 'xxx', null, 'aaa', 'bbb' union all
select	4, 'yyy', null, 'c', 'c' union all
select	5, 'yyy', null, 'd', 'd' union all
select	6, 'zzz', null, 'e', 'e'

select * From @tmp

-- do the work
update		t
set		t.seqnumber = 1 + t.id - q.m
from		@tmp t
inner join	(
			select		clienthash,
					min(id) m
			from		@tmp
			group by	clienthash
		) q on q.clienthash = t.clienthash
		
select * From @tmp

Peter Larsson
Helsingborg, Sweden

markusf
Starting Member

15 Posts

Posted - 2006-11-24 : 04:53:54

I came up with a solution:
1) create the temp table (where the result is initially iserted) with a Identity field
2) insert to Result table using the content of the identity-field into the SeqNumber field

:-)

Thanks for all the good input on this issue, guys + have a great weekend!

--
Markus Foss, Norway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources