All Forums Database
02 May 2013
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.

dnoeth 4628 posts Joined 11/04
02 May 2013

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

Dieter

You must sign in to leave a comment.