Numbering or Re-Numbering - 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)

Numbering or Re-Numbering

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-02-01 : 11:30:30

Hi, i have a table like this

code - sequence
13 1
13 3
13 4
13 5

44 2
44 3
44 7

i would like to have

13 1
13 2
13 3
13 4

44 1
44 2
44 3

how can i do this?

can I use identity and at each time that Code change
re-start Identity column to 1 ?

tks

C. Lages

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-01 : 11:42:48

CLages,

Do you mean something like this?

declare @Group int
declare @Sequence int
declare @LimitG int
declare @LimitS int
create table Listing (GRP int, SEQ int)
set @Group = 0
set @Sequence = 0
set @LimitG = 3
set @LimitS = 5
while @Group < @LimitG
begin
set @Group = @Group + 1
while @Sequence < @LimitS
begin
set @Sequence = @Sequence + 1
If @Sequence = @LimitS and @Group = 1
Break
PRINT 'Group ' + cast(@Group as char(1)) + ' Sequence ' + cast(@Sequence as char(1))
insert into Listing values(@Group,@Sequence )
end
set @Sequence = 0
end
select * from Listing

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-01 : 12:07:35

how bout we loose that loop?


declare @table table (id int, rank int)
insert into @table
select 1, 1 union all
select 1, 3 union all 
select 1, 6 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 11 union all
select 3, 12 union all 
select 3, 14 union all 
select 3, 15 

select * from @table 


declare @cnt int
set @cnt = 0
UPDATE 	t1
SET 	@cnt = rank = 	case when 
			exists (select top 1 * from @table where id < t1.id)
			and not exists (select top 1 * from @table where id=t1.id and rank < t1.rank)
			then 1
			else @cnt + 1
			end
from @table t1

select * from @table

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-01 : 12:09:21

does your table have a primary key? (hopefully) if so, then there is a relatively easy and quick solution. If not, time to read up on database theory. we need a little more sample data and a clue as to the structure of your table, in addition to what ORDER BY we should use to determeine which row gets which sequence value. (i.e, is it by some datetime column, or an indentity column, or some other factor?)

- Jeff

CLages
Posting Yak Master

116 Posts

Posted - 2005-02-01 : 12:12:42

not exactily, because you are inserting a table using this rules
and i already have a table and need to do something to RENUM de field
sequence.

i need this to send this table to the Government Office

(Government stuff)
who knows how they think, if they think.

C. Lages

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-01 : 12:18:42

quote:

Originally posted by spirit1

how bout we loose that loop?


declare @table table (id int, rank int)
insert into @table
select 1, 1 union all
select 1, 3 union all 
select 1, 6 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 11 union all
select 3, 12 union all 
select 3, 14 union all 
select 3, 15 

select * from @table 


declare @cnt int
set @cnt = 0
UPDATE 	t1
SET 	@cnt = rank = 	case when 
			exists (select top 1 * from @table where id < t1.id)
			and not exists (select top 1 * from @table where id=t1.id and rank < t1.rank)
			then 1
			else @cnt + 1
			end
from @table t1

select * from @table

Go with the flow & have fun! Else fight the flow

I presumed that CLages' data wouldn't be static, thus the dynamics of a loop.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

dsdeming

479 Posts

Posted - 2005-02-01 : 12:20:02

I was pretty happy with my code:

SET NOCOUNT ON
CREATE TABLE #a ( id int, rank int )

INSERT INTO #a SELECT 13, 1
INSERT INTO #a SELECT 13, 3
INSERT INTO #a SELECT 13, 4
INSERT INTO #a SELECT 13, 5
INSERT INTO #a SELECT 44, 2
INSERT INTO #a SELECT 44, 3
INSERT INTO #a SELECT 44, 7

SELECT * FROM #a

UPDATE #a
SET rank = x.newrank
FROM #a
JOIN (
SELECT a1.id, a1.rank, newrank = COUNT(a1.rank)
FROM #a a1
join #a a2 ON a1.id = a2.id and a1.rank >= a2.rank
group by
a1.id, a1.rank
) x ON #a.id = x.id AND #a.rank = x.rank

SELECT * FROM #a
DROP TABLE #a

Then I ran a quick test of query plans. I defer to Spirit1. His code is 20% faster.

Dennis

CLages
Posting Yak Master

116 Posts

Posted - 2005-02-01 : 13:08:36

I adjusted Dsdemig Script to my need and works fine

tks
C. Lages

by the way my adjustmentm because my table has 2 keys

UPDATE Dectmp54
SET tmp54_sequencia = x.newtmp54_sequencia FROM Dectmp54
JOIN (
SELECT a1.tmp54_cgc, a1.tmp54_numero, a1.tmp54_sequencia, newtmp54_sequencia = COUNT(a1.tmp54_sequencia)
FROM Dectmp54 a1
join Dectmp54 a2 ON a1.tmp54_cgc = a2.tmp54_cgc and
a1.tmp54_numero = a2.tmp54_numero and
a1.tmp54_sequencia >= a2.tmp54_sequencia
group by
a1.tmp54_cgc,
a1.tmp54_numero,
a1.tmp54_sequencia
)

x ON Dectmp54.tmp54_cgc = x.tmp54_cgc AND
Dectmp54.tmp54_numero = x.tmp54_numero AND
Dectmp54.tmp54_sequencia = x.tmp54_sequencia

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 01:39:37

declare @table table (id int, rank int)
insert into @table
select 1, 1 union all
select 1, 3 union all
select 1, 6 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 11 union all
select 3, 12 union all
select 3, 14 union all
select 3, 15

Declare @mainflag int,@seqno int
set @seqno =0
update @table set @seqno=rank=case when id <> @mainflag then 1 else @seqno+1 end,
@mainflag=id
from @table

select * from @table

Enjoy working

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-02 : 02:30:23

Here is other method

declare @table table (id int, rank int)
insert into @table
select 1, 1 union all
select 1, 3 union all
select 1, 6 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 11 union all
select 3, 12 union all
select 3, 14 union all
select 3, 15

select id,Rank=(select count(*) from @table where id=T.id and rank<=T.Rank) from @table T

Madhivanan

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 09:33:33

Through the review of all the solutions provided, I'm still wondering why none of them take into consideration this: what if you DIDN'T know the figures you were going to process. That's why I set up this loop...

declare @Group int
declare @Sequence int
declare @LimitG int
declare @LimitS int
create table Listing (GRP int, SEQ int)
set @Group = 0
set @Sequence = 0
set @LimitG = 3
set @LimitS = 5
while @Group < @LimitG
begin
set @Group = @Group + 1
while @Sequence < @LimitS
begin
set @Sequence = @Sequence + 1
If @Sequence = @LimitS and @Group = 1
Break
PRINT 'Group ' + cast(@Group as char(1)) + ' Sequence ' + cast(@Sequence as char(1))
insert into Listing values(@Group,@Sequence )
end
set @Sequence = 0
end
select * from Listing

...granted, you have to know the limits of the elements, but you don't have to hard-code your input either

.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-02 : 09:43:03

well, i assumed that he simply has a few groups that used to have correctly incremented numbers but then
he erased some rows and now he wants to "renumber" them again. at least that's how it is in a vast majority of cases.

xerxes i don't understand your commet about hardcoding it. who hardcoded what??

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-02 : 09:58:50

Xerxes -- your solution is generating a table called "Listing" which contains numbers and sequences which is great, but it has nothing to do with someone's existing data or solving the problem at hand.

I think you are confused with the data that you see the solution's providing -- it is just SAMPLE DATA so that you can cut and paste the code into Query Analyzer and run it to see the results. All of the INSERT statements are just adding rows to a table, which is NOT part of the solution, but rather a "proof of concept" so you can see that it works.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 10:34:20

quote:
Originally posted by spirit1

well, i assumed that he simply has a few groups that used to have correctly incremented numbers but then
he erased some rows and now he wants to "renumber" them again. at least that's how it is in a vast majority of cases.

xerxes i don't understand your commet about hardcoding it. who hardcoded what??

Go with the flow & have fun! Else fight the flow

Spirit1,

What I meant was he knew exactly what his input was. So you can set up select statements with the actual figures:

select 1, 1 union all
select 1, 3 union all
select 1, 6 union all
select 2, 2 union all.....

I was under the assumption that he wanted something generic, a routine that he could use for any sets/sequences.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources