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;

Do you want ties? Ie. if the 5th person is tied with another, do you want 6 rows returned? If not how do you want to choose between which person in the tie should be returned? — Brian DeMilia, Jul 06 '14 at 13:43

Nir Alfasi · Answer 1 · 2014-07-06T14:15:43.023

0

With Oracle's PLSQL you can do:

SELECT score.Student, Practical, Written, (Practical+Written) as SumColumn  
FROM ( SELECT Student, DENSE_RANK() OVER (ORDER BY Score DESC) as Score_dr
       FROM VOTES ) as score, students
WHERE score.score_dr <= 5
and score.Student = students.Student
order by score.Score_dr;

edited Jul 06 '14 at 14:15

answered Jul 06 '14 at 13:25

Nir Alfasi

53,191
11
86
129

When I run the statement it says error at line 3 missing expression – user3794621 Jul 06 '14 at 13:37
I cannot see that this is PLSQL. It looks like SQL to me. – user2672165 Jul 06 '14 at 13:37
It is not pl/sql. But it is Oracle specific syntax. – Brian DeMilia Jul 06 '14 at 13:41
@BrianDeMilia sorry I didn't know there is a difference: http://en.wikipedia.org/wiki/PL/SQL – Nir Alfasi Jul 06 '14 at 13:42
@user3794621 Anyways, it should work with Oracle 11g - I added "as" to the query which might help fixing the issue. – Nir Alfasi Jul 06 '14 at 13:43
I suspect that the issue was the use of `Student_dr` while the parameter was named: `Score_dr` – Nir Alfasi Jul 06 '14 at 13:46
Which line did you add "as" to the query? I tried to name the parameters the same but error at line 3 missing expression. – user3794621 Jul 06 '14 at 13:54
@user3794621 I'm not sure which version of my answer you are using. Just copy and paste the answer as it is now and try again. If you get an error again - please post the full error description here. – Nir Alfasi Jul 06 '14 at 14:01
SELECT score.Student, Practical, Written, (Practical+Written) as SumColumn FROM ( SELECT Student, , DENSE_RANK() OVER (ORDER BY Score DESC) as Score_dr FROM VOTES ) as score, students WHERE score.score_dr <= 5 and score.Student = students.Student order by score.Score_dr; ***error at line 3 missing expression**** , DENSE_RANK() OVER (ORDER BY Score DESC) as Score_dr – user3794621 Jul 06 '14 at 14:04
I have also tried replacing score.Score_dr in the last line of code with score.Student_dr but a no go. – user3794621 Jul 06 '14 at 14:10
Before `DENSE_RANK` there were two commas. – Nir Alfasi Jul 06 '14 at 14:15
Let's debug it together, try to run only the inner query: `SELECT Student, DENSE_RANK() OVER (ORDER BY Score DESC) as Score_dr FROM VOTES`. Fix this query and then try again. – Nir Alfasi Jul 06 '14 at 14:29
1

That `as Score` should be `Score`. No need of `AS` I believe for derived table. – Rahul Jul 06 '14 at 14:31
@Rahul you're right, but having this "as" will not cause any harm. The issue is with the inner query which is not structured well. – Nir Alfasi Jul 06 '14 at 14:33
operating binder does not exist, wrong number or types of arguments in call to score. – user3794621 Jul 06 '14 at 14:36
@user3794621 as I wrote - fix the inner query. – Nir Alfasi Jul 06 '14 at 14:39

Bohemian · Accepted Answer · 2014-07-07T08:26:04.777

0

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.

edited Jul 07 '14 at 08:26

answered Jul 06 '14 at 22:48

Bohemian

412,405
93
575
722

As I pointed out in your answer to the OP's other, astonishingly similar question, this approach has problems when it comes to handling ties. Personally I favour right results over portability but YMMV. – APC Jul 07 '14 at 07:00
@APC Your tenacity has paid off: I thought about it more and a minor change (*left* join with non-equality condition to eliminate self-join) improves it a lot - all ties are now returned as you would expect/hope from this kind of query. – Bohemian Jul 07 '14 at 08:23

score 0 · Answer 3 · answered Jul 07 '14 at 07:14

You can easily include the projection of the first query in the sub-query of the second.

SELECT Student
       , Practical
       , Written
       , tot_score 
FROM (
    SELECT Student
           , Practical
           , Written
           , (Practical+Written) AS tot_score 
           , DENSE_RANK() OVER (ORDER BY (Practical+Written) DESC) as Score_dr
     FROM Grades
    )
WHERE Student_dr <= 5
order by Student_dr;

One virtue of analytic functions is that we can just use them in any query. This distinguishes them from aggregate functions, where we need to include all non-aggregate columns in the GROUP BY clause (at least with Oracle).

Trying to figure out how to join these queries

3 Answers3