WITH t AS ( SELECT 'A' id, 2 rev, 'apple' dsc FROM dual UNION ALL SELECT 'A', 3, 'Mango' FROM dual UNION ALL SELECT 'B', 3, 'Durian' FROM dual UNION ALL SELECT 'B', 4, 'Dragon Fruit' FROM dual UNION ALL SELECT 'B', 5, 'Mango stin' FROM dual UNION ALL SELECT 'C', 1, 'Pear' FROM dual UNION ALL SELECT 'D', 2, 'Honey Due' FROM dual UNION ALL SELECT 'D', 3, 'Sugar Can' FROM dual ) SELECT * FROM (SELECT id, rev, dsc , ROW_NUMBER() OVER(PARTITION BY id ORDER BY rev DESC) rn FROM t ) WHERE rn = 1 ;