I am trying to use a window function, but it's not working. The subquery is effectively aggregating.I would advise avoiding using select-list scalar (and particularly correlated) subqueries when the query level in question also contains a group by. Do the group by in a subquery and then in the parent query pull in whatever subselects you want to execute.You need to make it easier for people to help. The first post you sent was just a window function. I asked fordetail and you sent a query without one. Given that discrepancy, added to the fact the query is question is not self-contained, I am less than inclined to try and figure out what you are trying to do.Given me a query that has a window function and looks like, if said window function was removed, it would successfully run, and I'll made an educated guess.
For my own part I need to remember that functional dependencies in the group by would allow one to forgo naming a column there that is present in the select-list...so maybe what you posted would work if run - but it still lacks your attempt at implementing a window function and I don't have the option to play with it since it is not self-contained.
David J.
