Did you tried with my above sql?
demo@ORA11G> select 1 acc_no, date'2015-01-01' open_dt from dual union all
2 select 2 acc_no, date'2017-01-01' open_dt from dual union all
3 select 3 acc_no, date'2016-01-01' open_dt from dual union all
4 select 4 acc_no, date'2018-01-01' open_dt from dual;
ACC_NO OPEN_DT
---------- -----------
1 01-JAN-2015
2 01-JAN-2017
3 01-JAN-2016
4 01-JAN-2018
so given this rule with the above data, the only violating record is acc_no=3.
....
. the rule is when you open an account then the account number and account open date should be greater than the previous account number and date. But the sequence has broken some how. so i need a sql query that find the account number and date that break the rule
....so running this SQL, return this data (only acc_no=3).
demo@ORA11G> select acc_no,open_dt
2 from (
3 select t.* ,
4 max(open_dt) over( order by acc_no,open_dt
5 rows between unbounded preceding and 1 preceding ) as max_dt,
6 max(acc_no) over( order by acc_no,open_dt
7 rows between unbounded preceding and 1 preceding ) as max_acc_no
8 from ( select 1 acc_no, date'2015-01-01' open_dt from dual union all
9 select 2 acc_no, date'2017-01-01' open_dt from dual union all
10 select 3 acc_no, date'2016-01-01' open_dt from dual union all
11 select 4 acc_no, date'2018-01-01' open_dt from dual ) t
12 )
13 where open_dt < max_dt or
14 acc_no < max_acc_no
15 /
ACC_NO OPEN_DT
---------- -----------
3 01-JAN-2016
demo@ORA11G>