CODEWITHSUNDEEP
mysqljoinsubquery
lomse
lomse

Reputation: 4165

Assign alias to a table in subquery MySQL

I have two tables:

  1. the exam table which contains the score performed by students
  2. and the student table

Both are joined this way: student.id=exam.student_id.

I am trying to get the firt five students who have the highest score calculated by the average of their score over 5 days using the query below: 

SELECT 
    student.id as std_id, 
    student.name, 
    (SELECT AVG(score) FROM exam WHERE exam.student_id=std_id ORDER BY exam.timestamp DESC LIMIT 5) AS score 

FROM student 
ORDER BY score 
DESC LIMIT 5

I am having the following error: 

#1054 - Unknown column 'std_id' in 'where clause'

I also tried it by replacing std_id by student.id but still no luck.

Any idea how to solve this issue? Great thanks

------------------------------------------------------------------------------

Sorry I made a mistake in my logic. As said earlier on, the average is calculated for

only the last 5 scores recorded

. Updated query: 

SELECT 
    student.id as std_id, 
    student.name, 
    (SELECT AVG(score) FROM (SELECT score FROM exam WHERE exam.student_id=student.id ORDER BY exam.timestamp DESC LIMIT 5) AS score) AS score 
FROM student 
ORDER BY score 
DESC LIMIT 5

The place which is giving the error is where I set exam.student_id=student.id

Thanks.

Upvotes: 1

Views: 10979

Answers (2)

Kickstart
Kickstart

Reputation: 21513

Your code looks like it should work to me (at least once the mod suggested by Niels Keurentjes has been done).

You could possibly use a generated sequence though rather than a correlated sub select. Something like thi:-

SELECT 
    student.id, 
    student.name, 
    AVG(Sub3.score)
FROM student
LEFT OUTER JOIN (
    SELECT Sub1.student_id, Sub1.score, @aSeq := IF(@PrevStudent = student_id, @aSeq + 1, 0) AS Sequence, @PrevStudent := student_id
    FROM (SELECT * FROM exam ORDER BY student_id, timestamp DESC) Sub1
    CROSS JOIN (SELECT @PrevStudent := 0, @aSeq := 0) Sub2
) Sub3
ON student.id = Sub3.student_id
WHERE (Sub3.Sequence IS NULL OR Sub3.Sequence < 5)
GROUP BY student.id, student.name

Upvotes: 1

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

Just replace std_id with student.id - the alias doesn't exist yet during the subquery compilation since the outer query cannot be compiled until the subquery is considered valid (and its output known).

Upvotes: 1

Related Questions