Row_number to not increment if values on three columns are the same from the row before
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
CurrPackageTrackingNowith the following logic:
- Count starts at 1
orderidis different from the
orderidon the previous row, then the count should reset to 1.
- If the
DocumentNo_is the same from the previous row but
CurrPackageTrackingNois different on the previous row, then the count should increment by 1
- If the
CurrPackageTrackingNoare all the same from the previous row, then count should remain at 1
So far Im able to get the logic on the first 3 bulletpoints using the query:
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?
Based on your description, what you want should be
dense_rank() over (partition by orderid order by DocumentNo_, CurrentPackageTrackingNo)