locked
How do I increment a number in a SELECT statement RRS feed

  • Question

  • In my select statement, I need a number -- say starting from 10 -- and I need this number incremented by 1 in each row. So if my select statement returns 3 rows, I want to see this:

    RecordId -- MyNumber

    10189 -- 10
    19987 -- 11
    86878 -- 12

    In this example, the RecordId is coming from the table and the MyNumber is the arbitrary number I need incremented by 1 -- starting from 10.

    How can form this select statement?


    Thanks, Sam

    Friday, February 24, 2012 9:45 PM

Answers

  • Try this.

    Declare  @Source  table (RecordId int)
    insert into @Source
    select 10189
    union all
    select 19987
    union all
    select 86878
    
    select RecordId,Row_number() Over(Order by RecordId)+ 9 
    from @Source
    --If you dont care about the in which numbers are assigned
    select RecordId,Row_number() Over(Order by (select 0))+9
    from @Source
    You can use the starting number as a variable and use the variable-1 in place of 9


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Friday, February 24, 2012 9:53 PM
    • Marked as answer by imsam67 Friday, February 24, 2012 9:53 PM
    Friday, February 24, 2012 9:50 PM

All replies

  • Try this.

    Declare  @Source  table (RecordId int)
    insert into @Source
    select 10189
    union all
    select 19987
    union all
    select 86878
    
    select RecordId,Row_number() Over(Order by RecordId)+ 9 
    from @Source
    --If you dont care about the in which numbers are assigned
    select RecordId,Row_number() Over(Order by (select 0))+9
    from @Source
    You can use the starting number as a variable and use the variable-1 in place of 9


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Friday, February 24, 2012 9:53 PM
    • Marked as answer by imsam67 Friday, February 24, 2012 9:53 PM
    Friday, February 24, 2012 9:50 PM
  • Thank you very much!!!

    Thanks, Sam

    Friday, February 24, 2012 9:53 PM