9

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

Salem Ahmed
  • 969
  • 2
  • 10
  • 17

2 Answers2

9

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.

Veera
  • 3,064
  • 2
  • 11
  • 25
  • could u plz explain why that works using 'inner_Query' ? – Salem Ahmed Nov 11 '14 at 13:18
  • 2
    It is not inner join, It is Sub query. The columns Nxt_Price and Prv_Price are created only inside the innerQuery. It is not possible to access them in the same query. So, we need to use another 'select' query to use the values of Nxt_Price and Prv_Price. – Veera Nov 11 '14 at 13:22
3

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
Ben Thul
  • 27,049
  • 4
  • 40
  • 62