Do I need ORDER BY in a window, if I already have a properly ordered query?

Question

In this query..

SELECT *
FROM foo
ORDER BY x;

Does a window function over foo need a specific ORDER BY clause or does the window share the ordering of the query?

Question inspired by @Erwin, and @Ypercube's comments on my own answer

Answer 1

Yes

You can actually see that without going to the spec.

CREATE TABLE foo AS
  SELECT trunc(random()*10) AS x
  FROM generate_series(1,100);

with

SELECT row_number() OVER (), x
FROM foo
ORDER BY x;

 row_number | x 
------------+---
         95 | 0
         93 | 0
         75 | 0
         74 | 0
         21 | 0
         55 | 0
         97 | 0
         54 | 0
         26 | 0
          5 | 0

Versus the window function over an ordered partition

SELECT row_number() OVER (ORDER BY x), x
FROM foo;

 row_number | x 
------------+---
          1 | 0
          2 | 0
          3 | 0
          4 | 0
          5 | 0
          6 | 0