Unique gapless ID:s from system - how to?

Database
Enthusiast

Unique gapless ID:s from system - how to?

After googling around as well as browsing this forum, I failed to find the solution to get gapless sequential ID:s from a TeraData system - is there one? Conflicting information on usefulness of e.g. Identity was offered. In case my post has already a detailed how-to answer somewhere on these forums, please point me to it. Thanking you all for your help already.

Tags (2)
1 REPLY
Senior Apprentice

Re: Unique gapless ID:s from system - how to?

The only way to get a gapless sequence is to do it on your own with ROW_NUMBER like

select 
row_number() over (order by whatever)
+ coalesce((select min(seq) from tab), 0) as seq
....
from tab

Instead of "select min(seq) from tab" you might have an extra table storing with the current max value for this table and then you increase it by the activity_count (probably using a SP).

Dieter