SELECT ID , MAX(NAME) NAME , MAX(RANK) RANK FROM (SELECT '001' ID , 'JAM1' NAME , '1' RANK FROM DUAL UNION ALL SELECT '001' ID , 'JAM2' NAME , '2' RANK FROM DUAL UNION ALL SELECT '001' ID , 'JAM3' NAME , '3' RANK FROM DUAL UNION ALL SELECT '002' ID , 'MIN1' NAME , '1' RANK FROM DUAL UNION ALL SELECT '002' ID , 'MIN2' NAME , '2' RANK FROM DUAL UNION ALL SELECT '002' ID , 'MIN3' NAME , '3' RANK FROM DUAL ) GROUP BY ID UNION ALL SELECT ID , MIN(NAME) NAME , MIN(RANK) FROM (SELECT '001' ID , 'JAM1' NAME , '1' RANK FROM DUAL UNION ALL SELECT '001' ID , 'JAM2' NAME , '2' RANK FROM DUAL UNION ALL SELECT '001' ID , 'JAM3' NAME , '3' RANK FROM DUAL UNION ALL SELECT '002' ID , 'MIN1' NAME , '1' RANK FROM DUAL UNION ALL SELECT '002' ID , 'MIN2' NAME , '2' RANK FROM DUAL UNION ALL SELECT '002' ID , 'MIN3' NAME , '3' RANK FROM DUAL ) GROUP BY ID ORDER BY ID , RANK
WITH T(id, name, rank) AS ( SELECT '001', 'JAM1', 1 FROM DUAL UNION ALL SELECT '001', 'JAM2', 2 FROM DUAL UNION ALL SELECT '001', 'JAM3', 3 FROM DUAL UNION ALL SELECT '002', 'MIN1', 1 FROM DUAL UNION ALL SELECT '002', 'MIN2', 2 FROM DUAL UNION ALL SELECT '002', 'MIN3', 3 FROM DUAL ) SELECT id, name, rank FROM ( SELECT id, name, rank, DECODE(rank, MIN(rank) OVER(PARTITION BY id), 'true', MAX(rank) OVER(PARTITION BY id), 'true', 'false' ) AS minOrMax FROM T ) WHERE minOrMax = 'true' ;