SELECT T1.GOP,TO_CHAR(DAT,'YYYY-MM-DD') FROM TABLE1 T1,
(SELECT GOP,max(dat) CGOP FROM TABLE1 GROUP BY GOP) T2
WHERE T1.GOP=T2.GOP ORDER BY CGOP DESC,DAT;
CREATE TABLE TABLE1(GOP INTEGER,DAT DATE);
INSERT INTO TABLE1 VALUES(333,TO_DATE('2007-01-01','YYYY-MM-DD'));
INSERT INTO TABLE1 VALUES(222,TO_DATE('2007-01-02','YYYY-MM-DD'));
INSERT INTO TABLE1 VALUES(111,TO_DATE('2007-02-02','YYYY-MM-DD'));
INSERT INTO TABLE1 VALUES(222,TO_DATE('2007-05-05','YYYY-MM-DD'));
INSERT INTO TABLE1 VALUES(222,TO_DATE('2007-01-01','YYYY-MM-DD'));
INSERT INTO TABLE1 VALUES(111,TO_DATE('2007-01-10','YYYY-MM-DD'));
COMMIT;
SQL> SELECT GOP,TO_CHAR(DAT,'YYYY-MM-DD') FROM TABLE1;
已选择6行。
SELECT T1.GOP,TO_CHAR(DAT,'YYYY-MM-DD') FROM TABLE1 T1,
(SELECT GOP,COUNT(GOP) CGOP FROM TABLE1 GROUP BY GOP) T2
WHERE T1.GOP=T2.GOP ORDER BY CGOP DESC,DAT;