Lag() function with WHERE clause

Question

I have this sql function that works properly:

SELECT out_hum ,
 (out_hum - LAG(out_hum, 1) OVER (ORDER BY id)) As dif
FROM excel_table

But I want to select all the out_hum when the diferrence (dif) is equal to 0 or grater than a value. When I type this code I get an error...

SELECT out_hum ,
 (out_hum - LAG(out_hum, 1) OVER (ORDER BY id)) As dif
FROM excel_table  WHERE dif=0

How I can solve this?

Answer 1

The where clause cannot access aliases to expressions defined in the select clause (because, basically, the former is processed before the latter). On top of that, there is a special restriction to window functions, which can not appear in the where clause of a query (they are allowed only in the select and order by clauses).

Some databases support the qualify clause, which works around the language limitation - but not MySQL unfortunately. A typical solution is to use a derived table, such as a subquery:

select *
from (
    select out_hum, out_hum - lag(out_hum) over (order by id) as dif
    from excel_table
) t
where dif = 0

Notes:

• parenthesis around the substraction are not necessary

• 1 is the default value of the second argument of lag(), so there is no need to specify it