Creating identity field that restarts when other field changes

Creating identity field that restarts when other field changes

Post by Thiago Alm » Thu, 04 Sep 2003 07:55:40


if I have a table with a field of type int and another field of type
varchar, how can I do an insert that will have the int field behave sort of
like an identity field but that restarts when the varchar one changes. For
example, the result would be like this:

IntField VarCharField
--------------------------------
1 ABC
2 ABC
3 ABC
1 DEF
1 GHI
2 GHI
3 GHI

I'd like to populate it with one insert select. The select statement would
obviously be ordered by the varchar field that would populate the varchar
field of the table.

INSERT TableName
SELECT ...

Thanks!
 
 
 

Creating identity field that restarts when other field changes

Post by Thiago Alm » Thu, 04 Sep 2003 08:33:34

Found something here.
http://www.yqcomputer.com/

Let me know of any other ways!



of

 
 
 

Creating identity field that restarts when other field changes

Post by rja.carneg » Thu, 04 Sep 2003 22:15:58

Perhaps with a trigger, that updates the sub-identity column
to "SELECT MAX(IntField) FROM table WHERE VarCharField = ...",
plus one. Then, any that are still NULL, you set to 1.

This reproduces some but nOt all of IDENTITY behaviour.
I.E., if you delete the newest row for 'ABC', or all the
rows, the deleted number is forgotten and will be re-used.
A fuller solution uses a trigger /and/ a separate table
holding seed values fodifferent sub-identities...

If you want to delete row 1 and have the old row 2 be
row 1 nOw, a live operation of "SELECT COUNT(*)...WHERE...
AND view1.column <= view2.column" may do it - was that in the
list already offered? Anyway, you could wrap that /in/
a view, I think.