Search code examples
sqloracle-databasedatetimelaglead

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

Output

>>>>>sqlfiddle <<<<<

Solution

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