Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Pittsburgh, PA

    Question Unanswered: Inserting an unique row count

    This is for SQL Server...

    I need to insert counting value into a column called [count_id], which currently has a value of 0 for every record. The table has two other fields: [zip_code] and [criteria_value], the table is called [demographic_data].

    Currently I am using the following script to update the column:

    declare @count as integer
    set @count = 0

    while exists (Select * from data_transition..demographic_data where count_id = 0)


    set @count = (@count + 1)

    update demographic_data
    set count_id = @count
    where count_id = 0
    and zip_code in (select top 1 zip_code from demographic_data where count_id = 0)


    I don't want to use the identity column in SQL server, because it will apply a continuous number immediatley upon inserting the data into the table. Then some calculations are done on the table removing rows, so the [count_id] is updated from 0 after the unwanted rows are removed, and it is desirable to have the [count_id] continuous, for reasons not important here.

  2. #2
    Join Date
    Oct 2003

    try something like this

    select distinct field into #b from table

    select field,
    (select count(field) from #b where field >= b.field) as cnt
    from table b
    order by cnt

    --this sample code will count for you from 1, if you want to start at
    --zero then just put > instead of >=

    Hope this helps

  3. #3
    Join Date
    Jan 2003
    Pittsburgh, PA

    Thumbs up Re: try something like this

    Thanks, that did it. This gives me the faster performance that I was hoping to obtain!

Posting Permissions

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