create table Z_EMP
(
  id   NUMBER(10)  ,
  name VARCHAR2(400) not null,
  constraint  PK_EMP  primary key (ID)
);
create table Z_CONTACT
(
  id        NUMBER(14) not null,
  constraint PK_CONTACT primary key (ID),
  client_id NUMBER(10) not null,
  c_type    NUMBER,--1 -тлф, 2-email
  c_info    VARCHAR2(100 CHAR) not null,
  c_created DATE,
  active    CHAR(1)
);

create table Z_ADDRESSES
(
  id        NUMBER(14) not null,
  constraint PK_ADDR primary key (ID),
  client_id NUMBER not null,
  a_type    NUMBER,
  city      VARCHAR2(250 CHAR),
  street    VARCHAR2(250 CHAR),
  house     VARCHAR2(10),
  flat      VARCHAR2(10),
  created   DATE,
  active    CHAR(1)
);

select name,address,phone,email from (
  select 
    c.name,
    row_number() over (partition by a.client_id order by a.a_type desc,                      
                                                         decode(null, a.city,  -1,1) +
                                                         decode(null, a.street,-1,1) +
                                                         decode(null, a.house, -1,1) +
                                                         decode(null, a.flat,  -1,1) desc,
                                                         a.created desc)     as rn_addr,
    trim(BOTH ',' from a.city||','||a.street||','||a.house||','||a.flat)     as address,                                                   
    row_number() over (partition by cp.client_id order by cp.c_created desc ) as rn_phone,
    cp.c_info as phone,
    row_number() over (partition by ce.client_id order by ce.c_created asc ) as rn_email,
    ce.c_info as email
  from z_emp c,
       z_addresses a,
       z_contact  cp,
       z_contact  ce
 where 
 a.client_id (+)=c.id 
   and a.active    (+)='Y' 
   and cp.client_id(+)=c.id 
   and cp.active   (+)='Y' 
   and cp.c_type   (+)=1
   and ce.client_id(+)=c.id 
   and ce.active   (+)='Y' 
   and ce.c_type   (+)=2
   )
where rn_addr =1 
  and rn_phone=1
  and rn_email=1
minus 
select t.name,
  trim(BOTH ',' from a.city||','||a.street||','||a.house||','||a.flat)  as address,                                                   
  p.c_info as phone, 
  e.c_info as email    
from (
  select 
    c.name,
    (select unique first_value(a.id)  over (partition by a.client_id order by a.a_type desc, 
                                                                               decode(null, a.city,  -1,1) +
                                                                               decode(null, a.street,-1,1) +
                                                                               decode(null, a.house, -1,1) +
                                                                               decode(null, a.flat,  -1,1) desc,
                                                                             a.created desc)
      from z_addresses a where a.client_id=c.id and a.active='Y') as addr_id ,
    (select unique first_value(cp.id) over (partition by cp.client_id order by cp.c_created desc ) from z_contact  cp where cp.client_id=c.id and cp.active='Y' and cp.c_type=1) id_phone,--61500000709934
    (select unique first_value(ce.id) over (partition by ce.client_id order by ce.c_created asc ) from z_contact  ce where ce.client_id=c.id and ce.active='Y' and ce.c_type=2) id_email
  from z_emp c
  ) t,
  z_addresses a, 
  z_contact p, 
  z_contact e
where a.id(+)= t.addr_id
  and p.id(+)= t.id_phone
  and e.id(+)= t.id_email;

select max(f_value) value from
(
with t(id, value,d ) as
(select 2, 'A', sysdate-1  from dual union all 
 select 1, 'A', sysdate-1  from dual union all 
 select 3, 'C', sysdate  from dual)
select first_value(value) over (order by d) f_value from t
)group by f_value;