PostgreSQL — is it possible to use named window in query’s ORDER BY clause?

I’m trying to do something along the lines of

SELECT id, sum(weight) OVER w
FROM foo
-- Use random() to break ties
WINDOW w AS (ORDER BY weight DESC, random())
ORDER BY w
LIMIT 1;

but it errors out with

ERROR:  column "w" does not exist
LINE 5: ORDER BY w

I can of course rewrite it as

SELECT id, sum(weight) OVER w
FROM foo
-- Use random() to break ties
WINDOW w AS (ORDER BY weight DESC, random())
ORDER BY weight DESC, random()
LIMIT 1;

But in addition to shortening the query (the full query I’m working on is much more complicated and has several window functions over w), I want to capture the same random() ordering that was used to calculate sum(weight), rather than have PostgreSQL generate a different, random tie-breaker.

Here’s a DB fiddle demonstrating the kind of data set I’m working on. I want to ensure that the second column in the query output will always be monotonically increasing. Is it guaranteed to do so, or does it just happen to do so in this simple synthetic example? My real query has a lot of lateral joins which are in fact designed to thwart the optimiser’s lifting constants out of it, so I’m not sure it will always hold.

Edit: as @laurenz-albe points out, this is not directly possible. However, in my specific case, I realised that I already have a value that can be directly sorted on, in sum(weight), like in this fiddle.

Answer

You cannot take the shortcut of using the window name in the ORDER BY clause. You will have to repeat the expressions there.

Advertisement

Answer