Will row_number () always break ties in the same way?

Question

Will the row_number() function sort the same data in the same way?

Answer 1

Not. The order in SQL is unstable, which means that the original sort order is not preserved. There is no guarantee that the analytic function or order by will return the results in the same order for the same key values.

You can always add a unique identifier as the last key in a sort to make it reproducible.

EDIT:

Note: order by irreproducibility is part of the SQL standard. Oracle documentation does not indicate otherwise. And, in general, I usually order unstable in databases (for equivalent key values). I would expect row_number() to behave the same.

If you need things in a specific order, you can add rowid to the order by clause (see here ). In fact, rowid can solve your problem without row_number() .