Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Adding line sequence numbers

    hi all

    I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

    I have a custom table that houses invoices on the details level. So for example i have:

    InvcNo
    00000001
    00000001
    00000001
    00000002
    00000002
    00000003

    and so forth

    What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

    SeqNo
    1
    2
    3
    1
    2
    1

    I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    Thanks...but I dont have 2005 (

    Is it possible to pull the example that is given in 2005 section in a prior version of SQL?

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can you uniquely identify each record for a specific InvcNo?

  5. #5
    Join Date
    Mar 2006
    Posts
    82
    Yes, I have an InvcKey column set up

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    create table #t1 (InvcKey int, InvcNo int)
    insert into #t1 select
    1,1 union all select
    2,1 union all select
    3,1 union all select
    4,2 union all select
    5,2 union all select
    6,3

    select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
    where a.InvcNo=b.InvcNo
    and a.InvcKey>=b.InvcKey
    group by a.InvcKey, a.InvcNo

    drop table #t1

  7. #7
    Join Date
    Mar 2006
    Posts
    82
    Ok, Ive got it...I should have played around some before asking further

    Code:
    SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
    FROM table c, table d
    WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
    GROUP BY c.InvcNo, c.InvcKey
    ORDER BY c.InvcKey
    Thanks for the help!

    tibor

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should probably use a JOIN.
    Using
    Code:
    FROM table1, table2, ... , tableN
    Is not advised.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2006
    Posts
    82
    Being a self join, would I really need JOIN?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tibor
    Being a self join, would I really need JOIN?
    It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.

    Rather excitingly - you will have created a Theta Join when you have finished
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by pootle flump
    Rather excitingly - you will have created a Theta Join when you have finished
    neat! normally you'd have to pay $360k to get to that level!

Posting Permissions

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