Alternative to SQL window functions in Sybase

Question

I am working on Sybase Adaptive Server Enterprise (version 12.5.0.3). Trying to use Row_number() OVER (Partition by columnname order by columnname). When I execute the query it is throwing an exception saying that the syntax near OVER is incorrect. I have searched for proper row_number() syntax for sybase database, but there is nothing wrong in the syntax. I guess that the Sybase version that am using does not support row_number() OVER. I even tried dense_rank() OVER, but am getting the same error.

I need to know whether it is really a syntax issue or its because of Sybase's low version which is not supporting the key words?

If the issue is with the version, then is there any alternative for row_number OVER and dense_rank() OVER for sybase database.

My Query:

select  cr.firstname, cr.lastname, cr.dob,cr.phone,
          row_number() over (patition by cr.dob order by createddate) "rank"       
          from ff.CrossReferenceTable cr

Error Message:

Server Message:  Number  156, Severity  15
               Server 'MyServer', Line 1:
               Incorrect syntax near the keyword 'over'.

SQL Advantage is currently at Version 11, and I don't think that SQL Anywhere had a version 12.5.1, so it's likely you are using either Sybase ASE, or Sybase IQ. Can you please clarify what DBMS you are using? — Mike Gardner, Jul 31 '14 at 13:00
I have executed the below query and found the output. `Select @@version` and the output is **"Adaptive Server Enterprise/12.5.0.3/EBF 10690 IR/P/RS6000/AIX 4.3.3/rel12503/1915/32-bit/FBO/Thu Jan 23 12:19:08 2003"** — user337986, Jul 31 '14 at 15:25
Sybase ASE 12.5 does not support `row_number` or `over`. Here is a workaround for `row_number` suggested by someone using temp tables. http://stackoverflow.com/questions/16608469/how-to-set-row-number-in-sybase-query — Mike Gardner, Jul 31 '14 at 16:33
Also, here's the documentation link: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_12.5.1/title.htm You will likely find the *Transact SQL User's Guide*, *Reference Manual: Building Blocks* and *Reference Manual: Commands* to be helpful in figuring out which commands are support. — Mike Gardner, Jul 31 '14 at 16:39
hmm ... the query as written has a typo in "partition" that would result in the error message shown. — Rob, Nov 11 '17 at 19:34

score 3 · Answer 1 · edited Jan 09 '20 at 07:54

Right, unfortunately Sybase ASE doesn't support row_number() function as well as rank() and dense_rank(). However, in some simple cases, where partition clause is not used it could be converted in the way like

select rank=identity(music), * into #new_temp_tab1 from CrossReferenceTable order by createddate
select firstname, lastname, dob, phone, rank from #new_temp_tab1

In your case it's going to be a little bit more complicated, I can recommend using cursor with temporary table to emulate row_number() over partition by behavior. Please have a look at the example below:

create table CrossReferenceTable
(
    firstname varchar(50),
    lastname varchar(50), 
    dob int,
    phone char(10), 
    createddate date
)
go

create proc sp_CrossReferenceTable
as
begin
    declare @i int
    declare @cur_firstname varchar(50)
    declare @cur_lastname varchar(50)
    declare @cur_dob int
    declare @cur_phone varchar(10)
    declare @cur_rank int

    declare cur cursor for 
        select 
                cr.firstname, 
                cr.lastname, 
                cr.dob,
                cr.phone, 
                count(*) AS "rank" 
            from 
                CrossReferenceTable cr
            group by 
                cr.dob 
            order by
                cr.dob, 
                createddate

    CREATE TABLE #CrossReferenceTable_TEMP 
    (
        firstname varchar(50),
        lastname varchar(50), 
        dob int,
        phone char(10),
        rank INT
    )

    open cur
    fetch cur into 
        @cur_firstname, 
        @cur_lastname, 
        @cur_dob, 
        @cur_phone, 
        @cur_rank

    set @i = @cur_rank

    while @@SQLSTATUS = 0 
    begin
        if @i = 0
            set @i = @cur_rank

        insert into #CrossReferenceTable_TEMP 
        select 
            @cur_firstname, 
            @cur_lastname, 
            @cur_dob,
            @cur_phone, 
            case 
                when @cur_rank > 1 then @cur_rank - (@i - 1) 
                ELSE @cur_rank
            end as "rank"

        set @i = @i - 1

        fetch cur into 
            @cur_firstname,
            @cur_lastname, 
            @cur_dob, 
            @cur_phone, 
            @cur_rank
    end

    select 
            firstname, 
            lastname, 
            dob, 
            phone, 
            rank 
        from 
            #CrossReferenceTable_TEMP
end
go

exec sp_CrossReferenceTable

This answer had been flagged as "Not an answer", I suspect because of how messy it was without any formatting. I made an attempt to fix the formatting, but I'm not very familiar with this language (I think it's SQL? I don't normally write anything longer than 3 lines in SQL, so I wouldn't know the conventions for formatting longer code in it.) I suspect the big code block should probably have some indentations in it. Other than the formatting, this looks like a fine answer to me. — ArtOfWarfare, Oct 15 '14 at 11:59
@ArtOfWarfare Thank you for the formatting! You did it absolutely correctly. I hope the answer was useful. — Ispirer SQLWays Migrations, Oct 15 '14 at 12:29

score 1 · Answer 2 · edited Jan 07 '20 at 14:54

1

Try the generic query below to have same effect as ROW_NUMBER()

SELECT 
  A.MyPartitionColumn, 
  A.MyRunningNumberColumn, 
  ( SELECT count(*) 
    FROM MyTable 
    WHERE    MyPartitionColumn      = A.MyPartitionColumn
         AND MyRunningNumberColumn <= A.MyRunningNumberColumn
  ) AS "Row_Number"
FROM      MyTable  A
ORDER BY  MyPartitionColumn, MyRunningNumberColumn

edited Jan 07 '20 at 14:54

ChrisMM

8,448
13
29
48

answered Jan 07 '20 at 14:34

user11413536

11
1

Alternative to SQL window functions in Sybase

2 Answers2

Linked