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.
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.
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.
An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With