December 10, 2002 at 9:32 am
Hi, how would one go about adding a sequence number to a view? i.e., achieve the same effect as using 'someID' = IDENTITY(int, 100000001, 1) in a SELECT INTO?
thanks,
steph.
December 10, 2002 at 9:54 am
Sounds like you're looking for a ROWID() type function. There was a post a couple of days ago that did this by populating the column with a subselect. In a query that returns a large number of rows, the overhead of doing this would be staggering.
There's not a generally accepted "GOOD" way to do this in SQL.
December 10, 2002 at 10:04 am
FIrst, hom much data are you talking abou and is their a primary or unique clustered index on the table? There are a lot of ways to mimic it but can cause CPU overhead if too much data to deal with.
December 10, 2002 at 11:12 am
Hi,
the rowset from the underlying tables is small (<1000 rows). it's got a compound primary key on two char(4) fields.
i've got no problem deriving a 'unique' set of integers based values of the other columns, but what i'm after is a nice continuous sequence that starts at 1 and increments by one for each new row in the view.
steph.
quote:
FIrst, hom much data are you talking abou and is their a primary or unique clustered index on the table? There are a lot of ways to mimic it but can cause CPU overhead if too much data to deal with.
December 10, 2002 at 11:40 am
the "rowID" can be calculated with a subselect similar to:
select RowID=(select count(*)
from Table where xyz.Keys < Keys)
from Table xyz
For your 1000 rows, you will have an additional 1000 selects to get the ROWID.
Edited by - don1941 on 12/10/2002 11:41:23 AM
December 10, 2002 at 2:50 pm
Actually unless you want to start at 0 change
select count(*)
to
select count(*) + 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply