Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get the top row after order by in Oracle Subquery

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.

like image 715
James Avatar asked Aug 03 '10 06:08

James


People also ask

Can we use subquery in ORDER BY clause Oracle?

Order by clause does not works inside a Sub-Query.No use of giving ORDER BY clause inside the sub query. Subquery gives values to the outer query and outer query only orders the value based on the order by clause.

Can we use subquery in ORDER BY clause?

ORDER BY command cannot be used in a Subquery. GROUPBY command can be used to perform same function as ORDER BY command. Use single-row operators with singlerow Subqueries. Use multiple-row operators with multiple-row Subqueries.

Can subquery contain ORDER BY?

An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.


1 Answers

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;
like image 197
Bharat Avatar answered Oct 17 '22 07:10

Bharat