CODEWITHSUNDEEP
row-number
Mythri
Mythri

Reputation: 11

Row_number() over partition

I am working on peoplesoft. I have a requirement where I have to update the column value in a sequence ordered based on some ID. For eg. 

CA24100001648- 1
CA24100001648- 2
CA24100001664- 1
CA24100001664- 2
CA24100001664- 3
CA24100001664- 4
CA24100001664- 5
CA24100001664- 6

But, I am getting '1' as the value for all the rows on updating.

Here is my query, can anyone please help out on this.

UPDATE PS_UC_CA_CONT_STG C 
SET C.CONTRACT_LINE_NUM2 = ( SELECT row_number() over(PARTITION BY D.CONTRACT_NUM
                                                      order by  D.CONTRACT_NUM)
FROM PS_UC_CA_HDR_STG D 
WHERE C.CONTRACT_NUM=D.CONTRACT_NUM );

Thanksenter image description here

Upvotes: 1

Views: 278

Answers (1)

Abhishek Kumar
Abhishek Kumar

Reputation: 1

update emp a set comm = (with cnt as ( select deptno,empno,row_number() over (partition by deptno order by deptno) rn from emp) select c.rn from cnt c where c.empno=a.empno)

Upvotes: -1

Related Questions