Windowed Functions Can Only Appear in the Select or Order by Clauses

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

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

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

You seem to be trying to set customer names to sequential values. For this purpose, you don't need a cursor! Just something like this:

with toupdate as (
      select c.*, row_number() over (order by customerid) as seqnum
      from customer c
     )
update toupdate
    set customername = 'Customer' + convert(varchar(10), seqnum);

You should avoid cursors whenever you can. Set-based operations are more efficient and often result in simpler code.

Why no windowed functions in where clauses?

why can't I use a windowed function in a where clause in SQL Server?

One answer, though not particularly informative, is because the spec says that you can't.

See the article by Itzik Ben Gan - Logical Query Processing: What It Is And What It Means to You and in particular the image here. Window functions are evaluated at the time of the SELECT on the result set remaining after all the WHERE/JOIN/GROUP BY/HAVING clauses have been dealt with (step 5.1).

really I'm looking for the reasoning behind not being able to use
windowing functions in where clauses.

The reason that they are not allowed in the WHERE clause is that it would create ambiguity. Stealing Itzik Ben Gan's example from High-Performance T-SQL Using Window Functions (p.25)

Suppose your table was

CREATE TABLE T1
(
col1 CHAR(1) PRIMARY KEY
)

INSERT INTO T1 VALUES('A'),('B'),('C'),('D'),('E'),('F')

And your query

SELECT col1
FROM T1
WHERE ROW_NUMBER() OVER (ORDER BY col1) <= 3
AND col1 > 'B'

What would be the right result? Would you expect that the col1 > 'B' predicate ran before or after the row numbering?

Selecting row with ROW_NUMBER() window function

Your issue is that rowCol is an alias for a window function (ROW_NUMBER()) and they cannot appear in a WHERE clause. You can use a QUALIFY clause instead:

SELECT Employee.Salary_Grade_Id, 
       SUM(Salary_Grades.Grade_Amount) AS total, 
       ROW_NUMBER() OVER(ORDER BY Employee.Salary_Grade_Id) AS rowCol 
FROM Employee, Salary_Grades 
WHERE (Employee.Salary_Grade_Id = Salary_Grades.Grade_Id)
GROUP BY Employee.Salary_Grade_Id
QUALIFY rowCol = 1

Note that you should use explicit JOIN syntax and rewrite the query as

SELECT Employee.Salary_Grade_Id, 
       SUM(Salary_Grades.Grade_Amount) AS total, 
       ROW_NUMBER() OVER(ORDER BY Employee.Salary_Grade_Id) AS rowCol 
FROM Employee
JOIN Salary_Grades ON Employee.Salary_Grade_Id = Salary_Grades.Grade_Id
GROUP BY Employee.Salary_Grade_Id
QUALIFY rowCol = 1

Update with ROW_NUMBER() OVER

This isnt tested nor do you provide expected results or data structure. However, the issue is quite clear (as SMor has pointed out in the comments). This isnt the best code and the indentation is awful (i want it to be readable with scrolling left or right here on SO).

You most likely have to edit what Im joining on in the update statement at the end, because without further information from you i have to guess what the join predicate ought to be. Im sure you can figure it out from this point forward.

; WITH cte AS
    (
        SELECT
            [BREAG_BRE_ID]
            , CASE 
                WHEN x.[BREAG_BGL_ID] <> x.GROUP_ID THEN x.GROUP_ID 
                ELSE x.[BREAG_BGL_ID] 
                END As NewBREAGBGLID
            , CASE 
                WHEN x.[BREAG_BGL_ID] <> x.GROUP_ID THEN ROW_NUMBER() 
                OVER (ORDER BY x.[BREAG_BRE_ID] DESC) + 
                    (
                        SELECT ISNULL(MAX(BREAG_GUEST_SEQ), 0) 
                        FROM BOS_RESADDGUEST
                        WHERE DATEPART(YEAR, BREAG_DATEFROM) = DATEPART(YEAR, GETDATE()) 
                        AND BREAG_BGL_ID = x.GROUP_ID
                    )
                ELSE x.[BREAG_GUEST_SEQ] 
                END AS NewBREAGGUESTSEQ
        FROM
            (
                SELECT g2.*, g1.[BREAG_BGL_ID], g1.[BREAG_BRE_ID], g1.[BREAG_GUEST_SEQ]
                FROM 
                    BOS_RESADDGUEST G1
                INNER JOIN 
                    (
                        SELECT 
                            [BRE_ID]
                            ,MAX([BRE_DATEFROM]) AS DATEFROM
                            ,MAX([BRE_DATETO]) AS DATETO
                            ,MAX(BGL_ID) AS GROUP_ID
                        FROM 
                            BOS_RESERVATION
                            LEFT JOIN BOS_UNIT_LIST ON BUL_ID = BRE_UNIT_ID
                            LEFT JOIN BOS_UNITTYPE_LIST ON BUL_UNITTYPE_ID = BUT_ID
                            LEFT JOIN BOS_GROUP_LIST ON BGL_ID = BUT_GROUP_ID
                        WHERE
                            BRE_ID = @DIALOG_BRE_ID
                        GROUP BY [BRE_ID]
                    ) AS G2
                ON G2.[BRE_ID] = G1.[BREAG_BRE_ID]
            ) AS x
    )
UPDATE 
    [BREAG_BGL_ID] = c.NewBREAGBGLID
    , [BREAG_GUEST_SEQ] = c.NewBREAGGUESTSEQ    
FROM BOS_RESADDGUEST br
INNER JOIN cte c ON br.[BREAG_BRE_ID] =c.[BREAG_BRE_ID]

How to fix query for Window Function

You need to use a subquery like :

WITH 
T AS 
(
SELECT CASE
            WHEN SUM(ISNULL(epf.emp_contribution, 0)) OVER(PARTITION BY epf.emp_no, 
                                                                        epf.FinancialYear
                  ORDER BY(epf.pcm_year * 100 + pcm_month)) + SUM(ISNULL(epf.vpf, 0)) OVER(PARTITION BY epf.emp_no, 
                                                                                                      epf.FinancialYear
                  ORDER BY(epf.pcm_year * 100 + epf.pcm_month)) < 3000
            THEN SUM(ISNULL(epf.emp_contribution, 0)) OVER(PARTITION BY epf.emp_no, 
                                                                        epf.FinancialYear
                  ORDER BY(epf.pcm_year * 100 + pcm_month)) + SUM(ISNULL(epf.vpf, 0)) OVER(PARTITION BY epf.emp_no, 
                                                                                                      epf.FinancialYear
                  ORDER BY(epf.pcm_year * 100 + epf.pcm_month))
            ELSE NULL
         END AS CALC
FROM EmpPFContributionTest epf
)
UPDATE epf
SET    EmpContbtnWithoutTax = CALC
FROM   T
       JOIN epf ON ???

Why this LAG() Window function returns The Order by clause is invalid

Try the following modified query and see if this works for you?

with Tr as (
    select 
        d.Warehouse, t.Code, d.zDate, 
        t.ID, t.QtyIn, t.QtyOut, t.BalanceAfter
    from DocDtls d
    join Transactions t on d.DocNum = t.DocNum
)
select ID, Code, QtyIn, QtyOut, BalanceAfter,
  Lag(BalanceAfter,1,0) over (partition by Warehouse, Code order by Id) Prev_BlncAfter 
from Tr;

---

Related Topics