sql - 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