Assigning an ID to a sequence when using partition clauses in SQL

Question

When attempting to identify a specific sequence using a partition by clause, it is possible to label each of the rows in a partition with row_number.

select *, 
       row_number() over(partition by x,y order by z desc) as rn,
from table ;

How would you go about assigning a specific ID like a hash to each partition?

Answer 1

You can enumerate the partitions using dense_rank():

select t.*,
       row_number() over (partition by x, y order by z desc) as with_partition_seqnum,
       dense_rank() over (order by x, y) as partition_seqnum
from t;