Row_number() equivalent

I see rank() and dense_rank(), but i don’t think there is a way to get the equivalent of (SQL) row_number() using QuickSight calculated fields.

Please correct me if there’s a way to do this with existing functionality.

I have use cases where if there is a tie on the record, i want it to randomly assign a record 1, and the other ties would continue without duplicate “ranks” and without holes.

https://prestodb.io/docs/current/functions/window.html

Hi @mangell,
Can you explain a bit further, perhaps with example data? denseRank() should work because is has partition as well as sort available, no?

image

Hi, no dense_rank doesn’t work. It creates ties (multiple values with 1 over the partitioned field). It should randomly pick a winner in the case of ties.

The easiest way i can explain it, is it would do the same thing that row_number() does in SQL from the athena v2 presto documentation i linked above.

*correction in image above - row 6 should = 1 for expected row_number

You might need to do this in SQL.