CODEWITHSUNDEEP
sqloracle-database
rohith
rohith

Reputation: 753

select 1 row(latest or oldest) if there are two records that are within a second

My data looks like below :

If there are two records that are within a second window and the DML_TYPE is not equal(example rows:(5,6), (7,8), (9,10)), return only 1 record(either DML_TYPE = "U"(5,7,9) or DML_TYPE="I"(6,8,10)).

Looking for standard sql(not vendor specific) query that should return rows :

 1,2,3,4,5,7,9
    or
    1,2,3,4,6,8,10

what i have tried so far : This returns all rows.

select coh.contractor_order_hid, coh.change_time, coh.DML_TYPE
from contractor_order_hist coh
join contractor_order_hist cohSOCN on coh.contractor_order_hid = cohSOCN.contractor_order_hid
where extract( day from(coh.change_time - cohSOCN.change_time)*24*60*60) < 1 and coh.dml_type != coh.dml_type;

thanks for your help in advance.

enter image description here

Upvotes: 1

Views: 203

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

You can use lag():

select coh.*
from (select coh.*,
             lag(change_time) over (order by change_time) as prev_change_time,
             lag(dml_type) over (order by dml_type) as prev_dml_type
      from contractor_order_hist coh
     ) coh
where prev_change_time is null or
      prev_change_time < change_time - interval '1' second or
      prev_dml_type = dml_type or
      prev_cml_type is null;

Upvotes: 1

Related Questions