SQL Order-Equivalence

3 days ago

The SQL standard requires that peer rows (rows with the same values in the ORDER BY clause) must be treated the same across different OVER clauses, ensuring consistent row numbering.
Some database systems do not guarantee consistent treatment of peer rows across OVER clauses, despite the SQL standard's requirements.
Order-equivalent OVER clauses (those with identical PARTITION BY and ORDER BY clauses) must produce the same row order, including peer rows.
Performance optimizations can lead to desirable behavior, such as opposite numbering in ORDER BY x and ORDER BY x DESC, even when not required by the standard.
To ensure correct opposite numbering, one can extend the ORDER BY clause to avoid peers or use mathematical operations to derive the opposite numbering from a single window function.
Avoiding non-determinism in SQL queries, such as by including primary key columns in ORDER BY clauses, is a good practice to ensure consistent results.

Hasty Briefsbeta