Sybase SQL: row number over a partition

Question

please could you help me? I am trying to understand how Sybase ase SQL works.

Basically I have a query like this:

select ClientId,StartDate,EndDate from TableName

And I need to create a temporary table where I will have

ClientID = A, StartDate 20180101, 1
ClientID = A, StartDate 20190101, 2
ClientID = A, StartDate 20200101, 3
ClientID = B, StartDate 20180101, 1
ClientID = B, StartDate 20190101, 2
ClientID = C, StartDate 20190101, 1
ClientID = C, StartDate 20200101, 2

Do you know how to create it? I looked around but I could not find any suitable solution (rownum does not work and creating

SELECT row_number = identity(32),t.*
INTO #TempTable

does not work (in this case I get [1,2,3,4,5,6,7] instead of [1,2,3,1,2,1,2]

Thank you for your help!

Answer 1

OP hasn't provided the table DDL, nor a sample set of insert statements, so a few assumptions:

• StartDate is a varchar() (for the sake of this answer; should be able to switch to date, datetime or datetime without any issues)
• EndDate is not required in the desired output (per OP's example output)
• the combination (ClientId, StartDate) is unique (otherwise the proposed answer - below - will not generate the desired results)

Sample data:

create table clients
(ClientId       varchar(10)     null
,StartDate      varchar(10)     null
,EndDate        varchar(20)     null
)
go

insert into clients values ('A', '20180101', null)
insert into clients values ('A', '20190101', null)
insert into clients values ('A', '20200101', null)

insert into clients values ('B', '20180101', null)
insert into clients values ('B', '20190101', null)

insert into clients values ('C', '20190101', null)
insert into clients values ('C', '20200101', null)
go

As @Impaler has mentioned, Sybase ASE does not support 'window functions' so we need to get a bit creative.

One idea using a self join:

select  c1.ClientId,
        c1.StartDate,
        count(*) as num

from    clients c1
join    clients c2

on      c1.ClientId   = c2.ClientId
and     c1.StartDate >= c2.StartDate

group by c1.ClientId, c1.StartDate
order by 1,2
go

 ClientId   StartDate  num
 ---------- ---------- -----------
 A          20180101             1
 A          20190101             2
 A          20200101             3
 B          20180101             1
 B          20190101             2
 C          20190101             1
 C          20200101             2

NOTE: This query may perform poorly for larger data sets and/or where there are no useful indexes, ymmv ...

---

---

Demonstrating what happens if the (ClientId, StartDate) pair is not unique ...

Assume our data set looks like:

insert into clients values ('A', '20180101', null)
insert into clients values ('A', '20190101', null)
insert into clients values ('A', '20200101', null)

insert into clients values ('B', '20180101', null)
insert into clients values ('B', '20190101', null)   -- duplicate (ClientId, StartDate)
insert into clients values ('B', '20190101', null)   -- duplicate (ClientId, StartDate)

insert into clients values ('C', '20190101', null)
insert into clients values ('C', '20200101', null)
go

The proposed query generates:

 ClientId   StartDate  num
 ---------- ---------- -----------
 A          20180101             1
 A          20190101             2
 A          20200101             3

 B          20180101             1
 B          20190101             6     -- oops, only 2 rows for 'B' and the wrong 'num' value for this row

 C          20190101             1
 C          20200101             2

If the proposed query does not work in OP's environment it may be necessary for OP to provide a minimal, reproducible example; in particular, provide a sample create table and insert into statements to adequately demonstrate the real data set.