Row_number to not increment if values on three columns are the same from the row before
-2
votes
1
answer
2651
views
Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the
So far Im able to get the logic on the first 3 bulletpoints using the query:
orderid
, DocumentNo_
, and CurrPackageTrackingNo
with the following logic:
* Count starts at 1
* If orderid
is different from the orderid
on the previous row, then the count should reset to 1.
* If the orderid
and DocumentNo_
is the same from the previous row but CurrPackageTrackingNo
is different on the previous row, then the count should increment by 1
* If the orderid
, DocumentNo_
, and CurrPackageTrackingNo
are all the same from the previous row, then count should remain at 1

select *
,row_number() over (partition by orderid order by packageNo_) as ranking
from tblsample
order by PackageNo_
But for rows 15-17 on the below screenshot, the ranking column has been incrementing when its supposed to stay at 1 for the mentioned rows. Can someone give me an insight on how I can fulfill that logic?

Asked by Keith Rivera
(617 rep)
May 9, 2022, 08:28 AM
Last activity: May 9, 2022, 08:49 AM
Last activity: May 9, 2022, 08:49 AM