Can you use GROUP BY and Window function together?

user_18320687 0
Viewed 34

This is the code I'm trying to use

From orders as o, o.items as it
Group by o.orderno
Select o.orderno, Max(it.price) OVER (PARTITION BY it.itemno ORDER BY o.orderno) as MaxPrice;

But SQL++ returns an error which states:

Cannot resolve alias reference for undefined identifier it (in line 3, at column 23)

I'm trying to figure out if both GROUP BY and a window function can be used in the same query.

n1ql couchbase nosql
1 Answers

WINDOW functions and GROUP/Aggregates can be used together. But WINDOW functions must depended on (including OVER clause expressions) constant, group expressions or aggregate functions.

No other expression is allowed. This is restriction of GROUP (The reason once you do group by, There is no way you can reduce other expression to single value with out aggregate). Example for GROUP BY restriction

user_60805360
Related