Sample Header Ad - 728x90

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 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 enter image description here 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? enter image description here
Asked by Keith Rivera (617 rep)
May 9, 2022, 08:28 AM
Last activity: May 9, 2022, 08:49 AM