CREATE TABLE test_yixl (ID VARCHAR2(20), phoneno VARCHAR2(20), rec VARCHAR2(20), call_date DATE);SELECT MAIN.ID, sub.cnt, MAIN.rec FROM test_yixl MAIN, (SELECT ID, count(1) cnt, MAX(call_date) maxdate FROM test_yixl GROUP BY ID ) sub WHERE MAIN.ID = sub.id AND main.call_date = sub.maxdate GROUP BY main.id, sub.cnt, MAIN.rec;
(ID VARCHAR2(20),
phoneno VARCHAR2(20),
rec VARCHAR2(20),
call_date DATE);SELECT MAIN.ID, sub.cnt, MAIN.rec FROM
test_yixl MAIN,
(SELECT ID, count(1) cnt, MAX(call_date) maxdate FROM test_yixl GROUP BY ID ) sub
WHERE MAIN.ID = sub.id AND main.call_date = sub.maxdate
GROUP BY main.id, sub.cnt, MAIN.rec;