ORACLE SQL Query records where timeinterval with previous record is less then eg 1 minute


From input table query all records where time-difference is less or equal then 1 minute.

Input table

Required Output


>>>>>sqlfiddle <<<<<


Posted 2014-07-11T14:53:29.777

Reputation: 72



Does this do what you want?

select *
from (select a.*,
             lag(sdt) over (partition by id order by sdt) as prevsdt,
             lead(sdt) over (partition by id order by sdt) as nextsdt
      from table_a a
     ) a
where sdt - prevsdt <= 1/(24*60) or
      nextsdt - sdt <= 1/(24*60);

It produces the desired output on SQL Fiddle.

Gordon Linoff

Posted 2014-07-11T14:53:29.777

Reputation: 748 006

+1 Beat me. Using LAG and LEAD seems to be the way to go here. – rsenna – 2014-07-11T15:02:07.960

Just a nitpick: I guess you don't really need the inner query, you could use the analytic functions directly on the first SELECT. – rsenna – 2014-07-11T15:04:53.937

1@rsenna You can't use analytic functions in the WHERE. – Bob – 2014-07-11T15:08:21.873

@Bob: Ok, got it. Thanks for the correction. – rsenna – 2014-07-11T15:09:50.460