Post reply

Auto Increment within Select statement

  • January 20, 2010 at 3:36 pm

    #136653

    Not sure if my subject describes what I need help on, but hopefully it gives some idea.

    So I'm looking for away to auto increment a column within a select statement.

    Test Person #5555 ItemA

    Test Person #5555 ItemL

    Test Person #12 ItemA

    Test Person #5555 ItemC

    Test John #1345 Item566

    Test John #91345 Item345

    So say this is my data set that contains Patients Last Name/First Name and a invoice number.

    I want to write a select statement that will pull all that information but add on an additional column that has an auto increment but only on invoice matches. So if there are 3 rows with the same invoice # I'd like it to auto increment a number. Having trouble explaining it in words, so I'll give another visual example.. this is what it would look like visually

    Test Person #5555 ItemA 1

    Test Person #5555 ItemL 2

    Test Person #12 ItemA 1

    Test Person #5555 ItemC 3

    Test John #1345 Item566 1

    Test John #91345 Item345 1

    Thanks.

  • January 20, 2010 at 4:08 pm

    #1106405

    the row_number() function will do what you are after, as long as you are in SQL 2005 and above;

    something like this will work:

    SELECT COL1,COL2,COL3,COL4,ROW_NUMBER() OVER (PARTITION BY COL1,COL2,COL3 ORDER BY COL1,COL2,COL3,COL4) As MySequence

    FROM MYTABLE

    note that since COL4 is not in the "partition by" section, there will be a different row number for each COL4.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 20, 2010 at 4:18 pm

    #1106406

    Looks like that worked perfect. Thank you so much!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply