WITH t(answer, registdate, phonenum) AS ( SELECT 2, '2018/09/19 04:00:00', '01033333333' FROM dual UNION ALL SELECT 2, '2018/09/19 03:00:00', '01011111111' FROM dual UNION ALL SELECT 2, '2018/09/19 02:00:00', '01022222222' FROM dual UNION ALL SELECT 2, '2018/09/19 01:00:00', '01011111111' FROM dual ) SELECT answer, registdate, phonenum , DECODE(rn, 1, ROW_NUMBER() OVER(PARTITION BY answer, rn ORDER BY registdate)) answercount FROM (SELECT answer, registdate, phonenum , ROW_NUMBER() OVER(PARTITION BY answer, phonenum ORDER BY registdate) rn FROM t ) ORDER BY answer, registdate DESC ;