Eu tenho um aluno da tabela (identificação, nome, departamento, idade, pontuação). Quero encontrar o aluno mais novo que tiver a maior pontuação (entre os mais jovens) de cada departamento. No SQL Server, eu posso usar o seguinte 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).

No entanto, no Oracle, você não pode usar a cláusula order by na subconsulta e não há limite / top como palavra-chave. Eu tenho que ingressar na tabela de alunos duas vezes para consultar o resultado. No oracle, eu uso o seguinte 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

Alguém tem alguma idéia de simplificar o SQL acima para oracle.