Get the top row after order by in Oracle Subquery
In addition to Bharat's answer, it is possible to do this using ORDER BY in the sub-query in Oracle (as point out by Jeffrey Kemp):
SELECT *FROM student s1WHERE s1.id IN (SELECT id FROM (SELECT id, ROWNUM AS rn FROM student s2 WHERE s1.department = s2.department ORDER BY age ASC, score DESC) WHERE rn = 1);If you use this method, you may be tempted to remove the sub-query and just use rownum = 1. This would result in the incorrect result as the sort would be applied after the criteria (you'd get 1 row that was sorted, not one row from the sorted set).