CODEWITHSUNDEEP
sybase
Edward
Edward

Reputation: 91

How to update group records for one field with increment number in Sybase?

I have a temp table which has two columns: one is Name and another RecordNumber. They looks like below:

Name              RecordNumber  Rownum
EMEA-1111-SCHD-1     0    1
EMEA-12362-SCHD-1    0    2
EMEA-12362-SCHD-1    0    3
EMEA-12362-SCHD-1    0    4
EMEA-12362-SCHD-1    0    5
EMEA-2191-SCHD-1     0    6
EMEA-2191-SCHD-1     0    7
EMEA-2191-SCHD-1     0    8

I need to update column "RecordNumber" with increment number starting with 1. Let say for EMEA-1111-SCHD-1 only one record, so RecordNumber should be updated to 1. For EMEA-12362-SCHD-1 four records, so RecordNumber should be updated to 1,2,3,4 accordingly. Basically, I need to have a result as:

Name              RecordNumber  Rownum
EMEA-1111-SCHD-1     1    1
EMEA-12362-SCHD-1    1    2
EMEA-12362-SCHD-1    2    3
EMEA-12362-SCHD-1    3    4
EMEA-12362-SCHD-1    4    5
EMEA-2191-SCHD-1     1    6
EMEA-2191-SCHD-1     2    7
EMEA-2191-SCHD-1     3    8

Is it possible to do it without cursor? Thank you, Ed. I added identity col rownum to make this records unique. Is any idea how to update result to have record number by group?

Upvotes: 1

Views: 2737

Answers (1)

kolchanov
kolchanov

Reputation: 2108

You could do this query only in Sybase IQ with analytical functions.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/BCGEEBHA.htm

I have't access to sybase IQ this time, so I can't check query, but I suppose the right query is something like

select name,
row_number() over (partition by name) as RecordNumber
from Table

AFAIK Sybase ASE has't this feature.

Update

I think you can create self join query like this

select t1.name,
t1.Rownum - t2.MinRowNum + 1
from Table as t1,
(select name, min (Rownum) as MinRowNum from Table group by name) as t2
where t1.name = t2.name
order by t1.name, t1.Rownum

Upvotes: 1

Related Questions