Menu
Will the function row_number() always sort the same data in the same way?
559
ROW_NUMBER will always generate unique values without any gaps, even if there are ties. RANK can have gaps in its sequence and when values are the same, they get the same rank. DENSE_RANK also returns the same rank for ties, but doesn't have any gaps in the sequence.
The ROW_NUMBER() function is self-explanatory, as you've already seen the data. It simply assigns a consecutive ranking to each row ordered by revenue. If two rows have the same value, they won't have the same ranking. The RANK() function creates a ranking of the rows based on the provided columns.
ROW_NUMBER function is a SQL ranking function that assigns a sequential rank number to each new record in a partition. When the SQL Server ROW NUMBER function detects two identical values in the same partition, it assigns different rank numbers to both.
The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required.
No. Ordering in SQL is unstable, meaning that the original sort order is not preserved. There is no guarantee that an analytic function or order by will return the results in the same order for the same key values.
You can always add a unique id as the last key in the sort to make it reproducible.
EDIT:
Note: the non-reproduciblity of order by is part of the SQL standard. Oracle documentation does not specify otherwise. And, in general, I ordering is usually not stable in databases (for equivalent key values). I would expect row_number() to behave the same way.
If you need things in a particular order, you can add rowid to the order by clause (see here). In fact, rowid may solve your problem without row_number().
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
