CODEWITHSUNDEEP
sqlsybaserow-number
Pythonista anonymous
Pythonista anonymous

Reputation: 8950

Alternative to row_number to create a unique column in Sybase?

The problem

I have certain temporary tables to which I want to add a column with a unique identifier.

Ideally, I would like this column to contain incremental integers (e.g. 1,2,3,4, etc).

Alternatively, as a second best, I can settle for unique values, regardless of order (eg AAA, AAB, etc).

My constraints

In Ms SQL I would typically do it with row_number(). Unfortunately, I am using a version of sybase (Adaptive Server Enterprise 16) which doesn't seem to support row_number() nor cross apply .

Reproducible example

The code below works in Ms SQL but not in Sybase. I have found loads of similar questions, but all the answers rely on some variation of row_number(), which doesn't work for me. If I run it, I get

incorrect syntax near the keyword 'over'

My code is:

CREATE TABLE #my_test (my_date datetime, my_value float NULL )

go

INSERT INTO #my_test SELECT '1-Nov-2021',100
INSERT INTO #my_test SELECT '2-Nov-2021',110
INSERT INTO #my_test SELECT '3-Nov-2021',100
INSERT INTO #my_test SELECT '4-Nov-2021',110
INSERT INTO #my_test SELECT '5-Nov-2021',108
INSERT INTO #my_test SELECT '6-Nov-2021',105

go


select 
*, row_number() over (order by (select NULL)) as my_id
from #my_test

Upvotes: 0

Views: 586

Answers (1)

markp-fuso
markp-fuso

Reputation: 34514

This is what the identity column attribute is for, eg:

CREATE TABLE #my_test
(my_id      int       identity      -- pick a data type that'll be big enough to hold all future values
,my_date    datetime
,my_value   float     NULL 
)
go

-- exclude identity column from INSERT (column_list):

INSERT INTO #my_test (my_date, my_value) SELECT '1-Nov-2021',100
INSERT INTO #my_test (my_date, my_value) SELECT '2-Nov-2021',110
INSERT INTO #my_test (my_date, my_value) SELECT '3-Nov-2021',100
INSERT INTO #my_test (my_date, my_value) SELECT '4-Nov-2021',110
INSERT INTO #my_test (my_date, my_value) SELECT '5-Nov-2021',108
INSERT INTO #my_test (my_date, my_value) SELECT '6-Nov-2021',105
go

select * from #my_test
order by my_date
go

 my_id       my_date                         my_value
 ----------- ------------------------------- ---------------------------
           1             Nov  1 2021 12:00AM                  100.000000
           2             Nov  2 2021 12:00AM                  110.000000
           3             Nov  3 2021 12:00AM                  100.000000
           4             Nov  4 2021 12:00AM                  110.000000
           5             Nov  5 2021 12:00AM                  108.000000
           6             Nov  6 2021 12:00AM                  105.000000

To add an identity column for a select into:

select  my_id2=identity(int),    -- define new identity column name and precision
        my_date,
        my_value
into    #my_test2
from    #my_test
order by my_date
go

select * from #my_test2
order by my_date
go

 my_id2      my_date                         my_value
 ----------- ------------------------------- ---------------------------
           1             Nov  1 2021 12:00AM                  100.000000
           2             Nov  2 2021 12:00AM                  110.000000
           3             Nov  3 2021 12:00AM                  100.000000
           4             Nov  4 2021 12:00AM                  110.000000
           5             Nov  5 2021 12:00AM                  108.000000
           6             Nov  6 2021 12:00AM                  105.000000

NOTES:

  • OP will want to read up on identity gaps and identity burn factor
  • primary issue is that if/when ASE is shutdown hard, upon starting up you'll see a significant gap between the last identity value and the new identify value
  • see identity_gap setting for create table and sp_chgattribute (for altering identity attribute of a current table)
  • for select/into see the identity_gap option
  • above code tested in ASE 16.0 SP04 GA

Upvotes: 1

Related Questions