Hi Tom,

suppose i have table like this,

Acc_number Opening_date (MM/DD/YYYY)
123 01-01-2015
125 01-02-2016
126 01-04-2016
128 01-01-2014 (break the rule)
129 01-07-2016

Here account number is generated with a opening date. 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.

Here, for the account number 128 opening date should be greater than the previous accounts opening date. I have thousand of account number in the table. So i need to find those accounts (128 account) and their opening date where there rule has been broken.

with rws as (
  select 1 id, date'2015-01-01' dt from dual union all 
  select 2 id, date'2017-01-01' dt from dual union all 
  select 3 id, date'2016-01-01' dt from dual union all 
  select 4 id, date'2018-01-01' dt from dual 
), prev as (
  select r.*, lag(dt) over (order by id) prev_dt
  from   rws r
)
  select * from prev
  where  dt < prev_dt;

ID  DT                    PREV_DT               
3   01-JAN-2016 00:00:00  01-JAN-2017 00:00:00