Using RANK might kead to duplicate key exception in the merge statement if there are 2 rows with identical column values because rank will assign same id to them, while row_number will still order them with diffrent ids (eg id=1, id2) which will not lead to duplicate key exception.