Incremental count column based on another column contents

Question

I need to populate a column with the running count based on another column contents. The table is like this:

    count     seq_num
     1        123-456-789
     1        123-456-780
     1        123-456-990
     2        123-456-789
     2        123-456-990

So, as the seq_num column changes, the counter restes to '1' and as the column repeats, the counter increments by 1.

This is using SQL2000, and the seq_num field is varchar.

Any ideas? I have seen similar things, but no luck yet.. Thanks in advance

Answer 1

If you're inserting, you can use a subquery:

insert into 
    table (count, seq_num) 
values 
    ((select count(*)+1 from table where seq_num = @seq)
    ,@seq)

Otherwise, you'll need to have a date on there or some way of telling it how to determine what was first:

update table 
set count = 
    (select count(*)+1 from table t2 
     where t2.seq_num = table.seq_num 
           and t2.insertdate < table.insertdate)

Answer 2

if you need to be able to continue updating this in the future, you might try this. It's a few steps but would fix it AND set it up for future use. (probably need to check my syntax - I mess with ORacle more now, so I may have mixed up some things - but the logic should work.)

first, create a table to contain the current counter level per sequence:

Create newTable (counter int, sequence varchar)

then, fill it with data like this: insert into newTable (select distinct 0 as Counter, sequence from table) This will put each sequence number in the table one time and the counter for each will be set at 0.

Then, create an update proc with TWO update statements and a bit of extra logic:

Create procedere counterUpdater(@sequence varchar) as

Declare l_counter as int;
select l_counter = counter
from newTable
where sequence = @sequence

--assuming you have a primary key in the table.
Declare @id int;
Select top 1 @id = id from table 
where sequence = @sequence
   and counter is null;

--update the table needing edits.
update table 
set counter = l_counter + 1
where id = @id

--update the new table so you can keep track of which 
--counter you are on
update newTable
set counter = l_counter + 1
where id = @id

Then run a proc to execute this proc for each record in your table.

Now you should have a "newTable" filled with the currently used counter for each record in the table. Set up your insert proc so that anytime a new record is created, if it is a sequence not already in the newTable, you add it with a count of 1 and you put a count of 1 in the main table. If the sequence DOES already exist, use the logic above (increment the count already in use the "newTable" and place that count as the counter value in the newTable and the mainTable.

Basically, this method decided to use memory in place of querying the existing table. It will become most beneficial if you have a large table with lots of repeated sequence numbers. If your sequence numbers only happen two or three times, you probably want to do a query instead when you update and then later insert:

First, to update: --find out the counter value Declare l_counter int select l_counter = max(counter) from table where sequence = @sequence

update table 
set counter = l_counter + 1
where id = (select top 1 id from table where sequence = @sequence
            and counter is null)

then run that for each record.

Then, when inserting new records:

Declare l_counter int
select l_counter = max(counter)
from table where sequence = @sequence

IsNull(l_counter, 0)

Insert into table
(counter, sequence) values (l_counter + 1, @sequence)

Again, I'm positive I've mixed-and-matched my syntaxes here, but the concepts should work. OF course, it's a "one at a time" approach instead of set based, so it might be a little inefficient, but it will work.