select cp_controlstate.id,
       cp_controlstate.project_ref,
       cp_controlstate.component_ref,
       cp_controlstate.control_ref,
       cp_controlstate.control_id,
       cp_controlstate.previus_state,
       cp_controlstate.last_state,
       cp_controlstate.previus_date,
       cp_controlstate.last_date
    from (select distinct(ca.id) id, ca.rev_id, max(ca.rev_id) over (partition by ca.id) as maxPreviousRevId, p.ref project_ref, comp.ref component_ref, c.ref control_ref, c.id control_id, ca.state previus_state, maxca.state last_state, to_timestamp(revinfoprevius.rev_timestamp / 1000) previus_date, to_timestamp(revinfolast.rev_timestamp / 1000) last_date from control_audit ca
        join (select ca2.id, max(ca2.rev_id) over (partition by ca2.id) as maxRevId, state from control_audit as ca2) maxca
            on ca.id = maxca.id and ca.rev_id < maxca.maxRevId --and ca.state <> maxca.state
        join revinfo revinfoprevius on ca.rev_id = revinfoprevius.rev
        join revinfo revinfolast on maxca.maxRevId = revinfolast.rev
        join component_audit caudit on maxca.maxRevId = caudit.rev_id
        join control c on ca.id = c.id
        join component comp on c.component_id = comp.id
        join project p on comp.project_id = p.id
    where maxca.state = 'RECOMMENDED' or maxca.state = 'REQUIRED'
    order by ca.id desc) cp_controlstate
where cp_controlstate.rev_id = cp_controlstate.maxPreviousRevId
  and previus_state != last_state;