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
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.