SELECT ROW() OVER(PARTITION BY attrib_1 ORDER BY attrib_2)ROW(), RANK(), and DENSE_RANK() all share the same functionality, which is assigning numbers to tuples inside a partition ordered by some attribute(s). However, they are different in terms of assigning those numbers in the case of ties:
ROW()assigns numbers incrementally to tuples with the same value(s) in a non-deterministic way. In others words, same tuple that share the same value(s) with other tuples can have different numbers in different executions in a non-deterministic way.RANK()assigns same number to tuples with the same value(s). Also, there will be a gap between the number assigned to tuples with same value(s) and the next tuple.DENSE_RANK()is similar toRANK()but doesn’t create gaps.