Why is order by required inside OVER when using LEAD in SQL?

Question

SELECT seller_name, sale_value,
  LEAD(sale_value) OVER(ORDER BY sale_value) as next_sale_value
FROM sale
ORDER BY sale_value

Am I right to understand that lead must compulsorily have over(order by..) because the SELECT is executed before the final ORDER BY statement?

Answer 1

The ORDER BY is required in the OVER clause, not in the outer query. So this is fine:

SELECT seller_name, sale_value,
       LEAD(sale_value) OVER (ORDER BY sale_value) as next_sale_value
FROM sale;

However, the results may be in any arbitrary order.

Why does LEAD() require the ORDER BY? Well the definition of LEAD() is to pull the value from the "next" row. However, SQL tables represent unordered (multi)sets. There is no next row unless a column or expression defines it -- and that is what the OVER ( . . . ORDER BY) defines.