Get the top row after order by in Oracle Subquery

Question

I have a table student(id, name, department, age, score). I want to find the youngest student who has the highest(among the youngest students) score of each department. In SQL Server, I can use following SQL.

select * from student s1 
where s1.id in 
(select s2.id from student s2 
where s2.department = s1.department order by age asc, score desc top 1).

However, in Oracle, you cannot use the order by clause in subquery and there is no limit/top like keyword. I have to join the student table with itself two times to query the result. In oracle, I use following SQL.

select s1.* from student s1, 
(select s2.department, s2.age, max(s2.score) as max_score from student s2, 
(select s3.department, min(s3.age) as min_age from student s3 group by s3.department) tmp1 where 
s2.department = tmp1.department and s2.age = tmp1.min_age group by s2.department, s2.age) tmp2 
where s1.department =tmp2.department and s1.age = tmp2.age and s1.score=tmp2.max_score

Does anyone have any idea to simplify the above SQL for oracle.

Answer 1

try this one

select * from
  (SELECT id, name, department, age, score,
  ROW_NUMBER() OVER (partition by department order by age desc, score asc) srlno 
  FROM student) 
where srlno = 1;

Answer 2

Oracle doesn't have a 'LIMIT' keyword; instead it has WHERE ROWNUM < your_limit.

Answer 3

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 (SELECT id, 
             name, 
             department, 
             age, 
             score,
             rownum as rn 
      FROM student
      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).