Lag() function with WHERE clause

user_4288993 0
Viewed 2443

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?

subquery window-functions where-clause mysql sql
1 Answers

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 an order by clauses).

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

user_106767160
Related