On Mon, Jul 21, 2025 at 11:02:36PM -0600, Merlin Moncure wrote: > On Mon, Jul 21, 2025 at 10:08 PM Nico Williams <n...@cryptonector.com> > wrote: > > I would have a HAVING clause that comes _before_ GROUP BY apply to > > window functions and a second one that comes _after_ GROUP BY apply to > > the grouping. > > I don't know...consider: > #1 SELECT lag(1) OVER(), max(v) FROM (SELECT 1 AS v) q HAVING true ; > ... > #2 SELECT lag(1) OVER(), max(v) FROM (SELECT 1 AS v) q HAVING true HAVING > lag(1) OVER() IS NULL; > > What does the HAVING clause apply to in #1? I think you might be in > trouble with the standard here. 2nd clause doesn't feel right in #2. The > basic problem is that HAVING does more than just 'syntax sugar subquery / > WHERE' and it just can't be hijacked to do something else IMO.
#2 would be a syntax error because the second HAVING did not come after a GROUP BY. #1 would not be a syntax error only because of the use of window functions before the HAVING. > Syntax simplifying > SELECT * FROM (<window function query>) WHERE col = x Yes. I'd rather that than QUALIFY. QUALIFY only makes sense because so many other RDBMSes have it and it's likely to get standardized. Nico --
