title: Assign alias to a table in subquery MySQL
body: <p>I have two tables: </p>
  
  <ol>
  <li>the <code>exam</code> table which contains the score performed by students</li>
  <li>and the student table</li>
  </ol>
  
  <p>Both are joined this way: student.id=exam.student_id.</p>
  
  <p>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: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am having the following error: </p>
  
  <pre><code>#1054 - Unknown column 'std_id' in 'where clause'
  </code></pre>
  
  <p>I also tried it by replacing std_id by student.id but still no luck. </p>
  
  <p>Any idea how to solve this issue? Great thanks</p>
  
  <pre><code>------------------------------------------------------------------------------
  </code></pre>
  
  <p>Sorry I made a mistake in my logic. 
  As said earlier on, the average is calculated for </p>
  
  <blockquote>
    <p>only the last 5 scores recorded</p>
  </blockquote>
  
  <p>.
  Updated query: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>The place which is giving the error is where I set exam.student_id=student.id</p>
  
  <p>Thanks.</p>