Windowed functions can only appear in the SELECT or ORDER BY clauses

Question

Can anyone explain why can't we use windowed functions in group by clause and why it's allowed only in SELECT and ORDER BY

I was trying to group the records based on row_number() and a column in SQL Server as like this:

SELECT Invoice from table1 group by row_number() over(order by Invoice),Invoice 

I am getting an error

Windowed functions can only appear in the SELECT or ORDER BY

I can select this row_number() in SELECT clause but I want to know why can't we use it group by?

Answer 1

Windowed functions are defined in the ANSI spec to logically execute after the processing of GROUP BY, HAVING, WHERE.

To be more specific they are allowed at steps 5.1 and 6 in the Logical Query Processing flow chart here .

I suppose they could have defined it another way and allowed GROUP BY, WHERE, HAVING to use window functions with the window being the logical result set at the start of that phase but suppose they had and we were allowed to construct queries such as

SELECT a,         b,         NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect   FROM YourTable   WHERE NTILE(2) OVER (PARTITION BY a ORDER BY b) > 1   GROUP BY a,             b,             NTILE(2) OVER (PARTITION BY a ORDER BY b)   HAVING NTILE(2) OVER (PARTITION BY a ORDER BY b) = 1 

With four different logical windows in play good luck working out what the result of this would be! Also what if in the HAVING you actually wanted to filter by the expression from the GROUP BY level above rather than with the window of rows being the result after the GROUP BY?

The CTE version is more verbose but also more explicit and easier to follow.

WITH T1 AS ( SELECT a,         b,         NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForWhere   FROM YourTable ), T2 AS ( SELECT a,        b,        NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForGroupBy FROM T1 WHERE NtileForWhere > 1 ), T3 AS ( SELECT a,        b,        NtileForGroupBy,        NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForHaving FROM T2 GROUP BY a,b, NtileForGroupBy ) SELECT a,        b,        NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect FROM T3 WHERE NtileForHaving = 1 

As these are all defined in the SELECT statement and are aliased it is easily achievable to disambiguate results from different levels e.g. simply by switching WHERE NtileForHaving = 1 to NtileForGroupBy = 1

Answer 2

You can work around that by placing the window function in a subquery:

select  invoice ,       rn from    (         select  Invoice         ,       row_number() over(order by Invoice) as rn         from    Table1         ) as SubQueryAlias group by         invoice ,       rn