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 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



  • Based on your description, what you want should be

    dense_rank() over (partition by orderid 
                           order by DocumentNo_, CurrentPackageTrackingNo)
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2