3

I distinctly recall that T-SQL will never let you mix LAG and WHERE. For example,

SELECT FOO
WHERE LAG(BAR) OVER (ORDER BY DATE) > 7

will never work. T-SQL will not run it no matter what you do. But does T-SQL ever let you mix LAG with HAVING?

Note: All that an answer needs to do is either give a theory-based or documentation-based reason why it does not, or give any example at all of where it does.

forpas
  • 160,666
  • 10
  • 38
  • 76
J. Mini
  • 1,868
  • 1
  • 9
  • 38
  • 1
    No, you need to use CTE or subquery to make it then `where` to filter – D-Shih Feb 05 '22 at 15:03
  • [Why no windowed functions in where clauses?](https://stackoverflow.com/a/33522288/5070879). WHERE - filter on row level, HAVING - filter on result of aggregation, QUALIFY* - filter on windowed fucntion level. QUALIFY is SQL language extension not supported by SQL Server – Lukasz Szozda Feb 05 '22 at 15:18
  • 1
    LAG is a window function. And window functions are processed after the `GROUP BY` and `HAVING`, but before the `ORDER BY`. So you can sort by a window function. In TeraData & Snowflake there's [QUALIFY](https://docs.snowflake.com/en/sql-reference/constructs/qualify.html). But in MS Sql Server you typically wrap it in a sub-query and filter outside the sub-query. – LukStorms Feb 05 '22 at 15:23
  • 1
    Tell me, do you class this as "mixing lag and where": `SELECT * FROM (SELECT LAG(x) OVER(ORDER BY y) xlag FROM z) a WHERE xlag = 1`? – Caius Jard Feb 05 '22 at 15:43

2 Answers2

4

From Logical Processing Order of the SELECT statement:

The following steps show the logical processing order, or binding order, for a SELECT statement......

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Window functions are evaluated at the level of SELECT, which comes after HAVING, so the answer is no you can't use window functions in the HAVING clause.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • I've always thought that list from the documentation was weird, it doesn't actually contain Window Functions, nor does it contain `APPLY` or `PIVOT` or `UNPIVOT` or `FOR XML/JSON`, and what is `ON` as opposed to `JOIN` – Charlieface Feb 05 '22 at 19:18
0

Having clause can only be used with Group by clause. In order to use Group by the listed columns should be aggregated using Group by columns. Group by can only be used with aggregate functions like min,max,sum,count functions. Hence it is not possible to combine having clause along with the LAG analytical function.

In order to use LAG and Having, one should use CTE or subquery.

Teja Goud Kandula
  • 1,462
  • 13
  • 26
  • Not exactly true; I'm aware of at least two databases out there that permit HAVING without GROUP BY – Caius Jard Feb 05 '22 at 15:46
  • @CaiusJard In that case, `HAVING` implies a `GROUP BY ()` or grouping by the empty set, which is not quite the same as using aggregates without either `HAVING` or `GROUP BY` (scalar vs vector aggregates) – Charlieface Feb 05 '22 at 19:13