March 17, 2003 at 10:07 am
I have a table with an alphanumeric id field.
ID
123ABC
123ABC
123ABC
234XYZ
234XYZ
I would like to add another ranking field that increments itself by 1 every time it encounters the same id and resets itself back to 1 when it encounters a different id.
ID RANK
123ABC 1
123ABC 2
123ABC 3
234XYZ 1
234XYZ 2
Is there a set based solution to update this second field or must I use a cursor ?
March 17, 2003 at 10:52 am
Of course it depends on whether you are inserting through a stored procedure or directly into the database...
Let's say you insert directly. You could just create an insert trigger to do something like:
UPDATE <table> SET <ID_Ranking_Column> = ( SELECT COUNT(*) FROM <table>
INNER JOIN Inserted ON Inserted.ID = <table>.ID )
WHERE <I assume you have a way to uniquely identify the row = match with the inserted rows>
An Insert stored procedure is a little easier - you just get a count before the insert and add 1.
Close?
Guarddata-
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply