Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Unanswered: Simple query to create column of sequential numbers

    Hi,
    Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?

    e.g given

    intval | Description| Cost
    0 | Descvalue0| 4.32
    2 | Descvalue2| 4.33
    3 | Descvalue3| 4.34
    8 | Descvalue8| 4.35

    change it to:

    intval | Description| Cost
    0 | Descvalue0| 4.32
    1 | Descvalue2| 4.33
    2 | Descvalue3| 4.34
    3 | Descvalue8| 4.35

    I think it might need a stored proc..

    Many Thanks
    greg

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    update daTable
    set intval = 
    (    select min(T.intval ) + count(*) -1
        from daTable T
        where T.intval <= daTable.intval 
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    32
    Thanks, that did the trick

Posting Permissions

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