1. Registered User
Join Date
Jun 2004
Posts
15

## Unanswered: Sequence Number Generation [Resolved]

Does anyone know an efficient method for generating a sequence number in the following form?

Starting with 2 columns
1 2
----
A X
A Y
B X
B Y
B Z
C X

I want to then generate a third column as follows:
1 2 3
-------
A X 1
A Y 2
B X 1
B Y 2
B Z 3
C X 1

The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.

Any ideas? Thanks.
Last edited by Jezza101; 02-03-05 at 12:07.

2. Registered User
Join Date
Dec 2004
Location
Sweden
Posts
74
Perhaps I'm totally out of line here, but my suggestion is .... *taking cover* ... a cursor. Do a cursor that loops through each post and update the third field according to the rule you stated. Then put that script into a trigger that updates every new post.
Since you have a clearly stated rule on how the third column is updated, it won't be much of a hassle to do that, right?

- Nephilim

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
how do you determine what the sequence is in order to assign that 3rd column?

answer: you have to use ORDER BY

therefore you can also match any row to the "previous" row based on its sequence using the same column(s) that you used in the ORDER BY

you actually don't need that sequence column at all!!

4. Registered User
Join Date
Dec 2004
Location
Sweden
Posts
74

## Well, yes and no ...

how do you determine what the sequence is in order to assign that 3rd column?

answer: you have to use ORDER BY

therefore you can also match any row to the "previous" row based on its sequence using the same column(s) that you used in the ORDER BY

you actually don't need that sequence column at all!!
Well, yes, perhaps you don't need the sequence number in order to determine the order of the posts, but as Jezza101 states in his/her orignal post, the problem here was to generate the sequencedata, not to discuss the need for the column, given that I haven't missed anything ....

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
actually, that's not quite right

the problem was "to join to the previous result of any row within any group quickly for future calculations"

the sequence number was merely an attempt to do that

6. Registered User
Join Date
Dec 2004
Location
Sweden
Posts
74

It's good that I'm not the only one reading the posts, four eyes beats two anytime. r937 is totally right, go ahead with his idea.

Note to self : Read all of the post before posting a reply ....

7. Registered User
Join Date
Mar 2003
Location
Bucharest, Romania
Posts
80

## Try this one ...

declare @tbl table(fld1 char(1), fld2 char(1))
insert into @tbl values('A', 'X')
insert into @tbl values('A', 'Y')
insert into @tbl values('B', 'X')
insert into @tbl values('B', 'Y')
insert into @tbl values('B', 'Z')
insert into @tbl values('C', 'X')

select T.fld1,T.fld2,count(*) as SeqId from @tbl T join @tbl T1 on T.fld1=T1.fld1 and T.fld2>=T1.fld2 group by T.fld1,T.fld2
order by T.fld1,T.fld2

Hope it helps,

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
that's exactly what i had in mind

nice one, ionut

9. Registered User
Join Date
Jun 2004
Posts
15
hehe ok yeah I often wonder if its worth trying to explain why I have a problem as this always happens!!

Lets make it simple, I want to convert a 4 million row table from 'to date' transactions to 'this time'.

So my table looks like
1 2 3
---------
A Jan 10
A Mar 10
A Apr 20
A Jun 15
B Jan 100
B Nov 120

But I want a table that looks like:

1 2 3
---------
A Jan 10
A Mar 0
A Apr 10
A Jun -5
B Jan 100
B Nov 20

ie, the current row - the previous row for any group of Column1.

I cant see an efficient way of doing this without adding a sequence number. I suppose you could join on the max(column2) where its less than the current column2 or something messy like that...?! Any suggestions welcome!

What I may do is order and add an identity column, but the destination application would normally generate the seq no as I had described in first post so would be nice to keep the consitency with this converted data. With the seq no/identity column added it becomes easy to locate the previous row.

Thanks very much for all input, it is always appreciated !

10. Registered User
Join Date
Jun 2004
Posts
15
Originally Posted by ionut calin
declare @tbl table(fld1 char(1), fld2 char(1))
insert into @tbl values('A', 'X')
insert into @tbl values('A', 'Y')
insert into @tbl values('B', 'X')
insert into @tbl values('B', 'Y')
insert into @tbl values('B', 'Z')
insert into @tbl values('C', 'X')

select T.fld1,T.fld2,count(*) as SeqId from @tbl T join @tbl T1 on T.fld1=T1.fld1 and T.fld2>=T1.fld2 group by T.fld1,T.fld2
order by T.fld1,T.fld2

Hope it helps,

lol i wrote all that rambling above and you had posted this, hey looks promising - nice 1, I knew someone would have a cunning plan

11. Registered User
Join Date
Jun 2004
Posts
15
wow, worked perfectly, and very fast too.

Thanks you very much, have a nice day!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•