select * from ( select a.idx, a.unse_divi, a.image, a.nickname, a.mtn_connect_num, a.title, a.sale_event, a.keyword, a.mtn_connect_info, a.mtn_status, a.mtn_status_msg, b.regdate, row_number() over (partition by a.idx order by b.regdate desc) as rnum, case when cr_p.consult_reserve_pay_cnt >= 1 then 'Y' else 'N' end as consult_reserve_pay_yn, ( select content from mask_tarot.tbl_review where site_divi = 'mask_tarot' and expose_yn = 'Y' and mt_idx = a.idx order by idx desc limit 1 ) as review_content, cst.rating_txt, coalesce(cst.consult_cnt, 0) as consult_cnt, coalesce(rvw_c.review_cnt, 0) as review_cnt from mask_tarot.tbl_master as a join mask_tarot.tbl_recommend as b on a.idx = b.mt_idx left join ( select mt_uniqid, count(*) as consult_reserve_pay_cnt from mask_tarot.tbl_master_consult_reserve where delete_yn = 'N' and divi = 'new_master' group by mt_uniqid ) as cr_p on a.uniqid = cr_p.mt_uniqid and a.sale_event->>'new_master' = 'Y' and a.sale_event->>'new_master_pay_consult' = 'Y' left join ( select mt_idx, rating_txt, consult_cnt from ( select mt_idx, rating_txt, count(*) as consult_cnt, row_number() over (partition by mt_idx order by count(*) desc) as rnum from mask_tarot.tbl_review where site_divi = 'mask_tarot' and expose_yn = 'Y' group by mt_idx, rating_txt ) as sub_cst where rnum = 1 ) as cst on a.idx = cst.mt_idx left join ( select mt_idx, count(*) as review_cnt from mask_tarot.tbl_review where site_divi = 'mask_tarot' and expose_yn = 'Y' group by mt_idx ) as rvw_c on a.idx = rvw_c.mt_idx where a.delete_yn = 'N' and a.status = '1' and a.auth_level in ('40' , '43') and a.mtn_status <> 'CRDY' and a.consult_divi->'admin' is null ) as t where rnum = 1 order by case t.mtn_status when 'RESV' then 1 when 'CONN' then 2 when 'IDLE' then 3 else 4 end, t.regdate desc limit 20