获取Oracle Subquery中排序后的第一行 [英] Get the top row after order by in Oracle Subquery
问题描述
我有一个表学生(id,姓名,部门,年龄,分数)。我想找到最年轻的学生(在最年轻的学生中)每个部门的得分。在SQL Server中,我可以使用以下SQL。
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).
但是,在Oracle中,不能在子查询中使用order by子句,并且没有limit / top喜欢关键字。我必须加入学生表与自己两次查询结果。在oracle中,我使用以下SQL。
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
任何人都有任何想法来简化上述SQL for oracle。
Does anyone have any idea to simplify the above SQL for oracle.
推荐答案
试试这个
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;
这篇关于获取Oracle Subquery中排序后的第一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!