select item, cnt from ( select item ,cnt = count(*) ,seqnum = row_number() over (partition by id order by count(*) DESC) from src_table group by item ) sub_qry where seqnum = 1;