Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Thumbs up 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. #2
    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. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Red face 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. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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 ....
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  7. #7
    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,
    ionut
    Who cares about Romanian football ???

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

    nice one, ionut

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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. #10
    Join Date
    Jun 2004
    Posts
    15
    Quote 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. #11
    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
  •