Trying to figure out how to join these queries

Question

I have a table named grades. A column named Students, Practical, Written. I am trying to figure out the top 5 students by total score on the test. Here are the queries that I have not sure how to join them correctly. I am using oracle 11g. This get's me the total sums from each student:

SELECT Student, Practical, Written, (Practical+Written) AS SumColumn 
FROM Grades;

This gets the top 5 students:

SELECT Student 
FROM ( SELECT Student,
              , DENSE_RANK() OVER (ORDER BY Score DESC) as Score_dr
       FROM Grades ) 
WHERE Student_dr <= 5
order by Student_dr;

Answer 1

The approach I prefer is data-centric, rather than row-position centric:

SELECT g.Student, g.Practical, g.Written, (g.Practical+g.Written) AS SumColumn 
FROM Grades g
LEFT JOIN Grades g2 on g2.Practical+g2.Written > g.Practical+g.Written
GROUP BY g.Student, g.Practical, g.Written, (g.Practical+g.Written) AS SumColumn 
HAVING COUNT(*) < 5
ORDER BY g.Practical+g.Written DESC

This works by joining with all students that have greater scores, then using a HAVING clause to filter out those that have less than 5 with a greater score - giving you the top 5.

The left join is needed to return the top scorer(s), which have no other students with greater scores to join to.

Ties are all returned, leading to more than 5 rows in the case of a tie for 5th.

By not using row position logic, which varies from darabase to database, this query is also completely portable.

Note that the ORDER BY is optional.