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
精彩评论