CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
user897237
user897237

Reputation: 623

Comparing row with prev row

I'm trying to select once rows, that have (some specified) same values as the previous row.

I can select what I want like the following:

SELECT dpr_ts
       , dpr_open
       , dpr_volume
       , LAG(dpr_open, 1, 0) over(ORDER BY dpr_ts) AS po
       , LAG(dpr_close, 1, 0) over(ORDER BY dpr_ts) AS pc
       , LAG(dpr_volume, 1, 0) over(ORDER BY dpr_ts) AS pv
  FROM dpr
 ORDER BY dpr_ts;

How can I specifiy in where clause , that only the duplicates should appear?

I mean I want something like(this doent work, but putting it only to get you an idea what i'm trying):

SELECT dpr_ts
      , dpr_open
      , dpr_volume
      , LAG(dpr_open, 1, 0) over(ORDER BY dpr_ts) AS po
where po = dpr_volume;

Thanks Greetings

Upvotes: 0

Views: 113

Answers (1)

user800014
user800014

Reputation:

I think what you want is:

select *
  from (
    select dpr_ts
         , dpr_open
         , dpr_volume
         , LAG(dpr_open, 1, 0) over(ORDER BY dpr_ts) AS po
      from dpr
     order by drp_ts
    ) x
where x.po = x.dpr_volume

Upvotes: 3

Related Questions