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.
Can you explain a bit further, perhaps with example data? denseRank() should work because is has partition as well as sort available, no?
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.