4 ms·
I'm a huge fan of window functions, but one restriction surprises me: in Postgres, you can't put a window function into a WHERE clause. Does anyone know if ther
by pjungwir 12y ago
I'm a huge fan of window functions, but one restriction surprises me: in Postgres, you can't put a window function into a WHERE clause. Does anyone know if there is a good reason for this, or is it just a bit of missing functionality?
- deleted 12y ago[deleted]
- JohnBooty 12y agoI'd love to know this too. I provided an example elsewhere in this discussion of a potentially-complex query I implemented with window functions. I wanted to do something like returning every row with a row_number between 1 and 3 - but discovered I couldn't put this into the WHERE clause. The solution I came up with was to define a CTE where one of the columns is the result of the row_number() function, and then I did a SELECT on the CTE that included the WHERE functionality I needed.