Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use LEAD and LAG In Where?

i have a table like this:

Number   Price    Type       Date         Time
------   -----    ----    ----------    ---------
23456    0,665     SV     2014/02/02     08:00:02
23457    1,3       EC     2014/02/02     07:50:45
23460    0,668     SV     2014/02/02     07:36:34
23461    0,668     SV     2014/02/02     07:37:34
23462    0,668     SV     2014/02/02     07:38:34
23463    0,668     SV     2014/02/02     07:39:34

For each record I need previous/next price. In this case, the query is simple.

Select Lag(price) over (order by date desc, time desc),
Lead(price) over (order by date desc, time desc)
from ITEMS

but i need the result Where Next price <> record price

My Query is

    Select Lag(price) over (order by date desc, time desc) Nxt_Price,
    Lead(price) over (order by date desc, time desc) Prv_Price
    from ITEMS
 Where Nxt_Price <> price

but it refused to use that variable

like image 590
Salem Ahmed Avatar asked Nov 11 '14 12:11

Salem Ahmed


People also ask

In what scenarios is the lag function useful?

LAG() : SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.

What is the use of lag?

LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

What is lead and lag in window functions?

The LAG and LEAD functions are OLAP ranking functions that return the value of their expression argument for the row at a specified offset from the current row within the current window partition.

What is the difference between lag and lead?

Lead and lag are both used in the development of the project schedule. Lead is an acceleration of the successor activity and can be used only on finish-to-start activity relationships. Lag is a delay in the successor activity and can be found on all activity relationship types.


2 Answers

Try the below query:

SELECT Nxt_Price, Prv_Price 
FROM
    (Select price, Lag(price) over (order by date desc, time desc) Nxt_Price,
    Lead(price) over (order by date desc, time desc) Prv_Price
    from ITEMS) AS InnerQuery
 Where Nxt_Price <> price

It may help you.

like image 66
Veera Avatar answered Sep 27 '22 18:09

Veera


Use a common table expression.

with myItems as (
     Select Lag(price) over (order by date desc, time desc) Nxt_Price,
    Lead(price) over (order by date desc, time desc) Prv_Price
    from ITEMS
)
select *
from myItems
where Nxt_Price <> Prv_Price
like image 26
Ben Thul Avatar answered Sep 27 '22 16:09

Ben Thul