开发者

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             开发者_如何学Python 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?


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜