compare i and i+1 element oracle

Question

I have table

CREATE table table_x 
(
        id NUMBER,  -- ID
        NUMBER_history NUMBER, --  consecutive number
        start_date date,    -- start_date  of next id=end_date of previous
        end_date date   -- should be >=start_date 
);
 INSERT INTO table_x VALUES  (14 , 1, '13-NOV-92' ,'14-NOV-92' );
 INSERT INTO table_x VALUES  (15 , 2, '14-NOV-92' ,'16-NOV-92' );
 INSERT INTO table_x VALUES  (19 , 3, '16-NOV-92' ,'18-NOV-92' );
 INSERT INTO table_x VALUES  (11 , 4, '18-NOV-92' ,'14-NOV-93' );
 INSERT INTO table_x VALUES  (17 , 5, '15-NOV-93' ,'16-NOV-93' );
 INSERT INTO table_x VALUES  (151 , 6, '16-NOV-93' ,'17-NOV-93' );
 INSERT INTO table_x VALUES  (139 , 7, '17-NOV-93' ,'18-NOV-93' );
 INSERT INTO table_x VALUES  (121 , 8, '19-NOV-93' ,'20-DEC-93' );
 INSERT INTO table_x VALUES  (822 , 9, '20-DEC-93' ,'20-DEC-93' );

I want write query where I can find where start_date of next row > end_date of previous. they must be equal.

I try to do something like that using NUMBER_history as counter. C-way where I organize cycle by variable i and compare i and i+1 (NUMBER_history and NUMBER_history+1)

select * INTO row_tblx from table_x where NUMBER_history=NUMBER_history and end_date<(select start_date from table_x where NUMBER_history=NUMBER_history+1);

but I must organize loop by n_counter from 1 to last NUMBER_history value and fetch data into multiple row. How can I do it?
I try

set serveroutput on
DECLARE
CURSOR cur IS
      SELECT * FROM table_x;
TYPE row_tblx_type
IS
TABLE OF cur%ROWTYPE;
row_tblx row_tblx_type;

  rowx  cur%ROWTYPE;
  nh_count NUMBER;
BEGIN
FOR NUMBER_history IN cur LOOP
select * INTO row_tblx from table_x where NUMBER_history=NUMBER_history and end_date<(select start_date from table_x where NUMBER_history=NUMBER_history+1);
DBMS_OUTPUT.PUT_LINE (row_tblx(NUMBER_history).id);
END LOOP;
END;
/

How can I do it using for or another loop, multiple records or table of records , cursor, an table row as counter (NUMBER_history)? How can I do it without cursor?

Answer 1

You don't need PL/SQL or a loop for that:

select *
from (
   select id, number_history, start_date, end_date, 
          lead(start_date) over (order by number_history) as next_start
   from table_x
) t
where next_start > end_date;