select c.id
      ,c.name
      ,max(a.city || ', ' || a.street || ', ' || a.house || ' fl. ' || a.flat)
           keep(dense_rank first order by a.a_type desc, nvl2(a.city, 1, 0) 
+ nvl2(a.street, 1, 0) + nvl2(a.house, 1, 0) + nvl2(a.flat, 1, 0) desc, a.created desc) address
      ,max(ph.c_info) keep(dense_rank first order by ph.created desc) phone
      ,max(em.c_info) keep(dense_rank first order by em.created asc) email
   from client c
     left join address a on a.client_id = c.id and a.active = 'Y'
     left join contact ph on ph.client_id = c.id and ph.c_type = 1 and ph.active = 'Y'
     left join contact em on em.client_id = c.id and em.c_type = 2 and em.active = 'Y'   
 group by c.id, c.name;