Why can i sort an inline SELECT value but not use it in a WHERE clause?

Question

I have this small SQL query.

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
AND `grade` >= 5
ORDER BY `grade` DESC

This gives me the error

1054 - Unknown column 'grade' in 'where clause'

But if i remove the 2nd last line, it works fine. I have tried to do AND a.grade and even give the tests table a name and append that name to grade but still no luck.

How can I use this inline query in a WHERE clause?

I have found that this works, but is it the only way?

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
AND (
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) >= 5
ORDER BY `grade` DESC

Answer 1

SELECT  a.`id` , a.`title` , a.`date` , 
        (
        SELECT  MAX( grade )
        FROM    tests
        WHERE   userid = 41
                AND presid = a.`id`
       ) AS grade
FROM    `presentations` a
WHERE   a.`visible` = 1
HAVING  `grade` >= 5
ORDER BY
        `grade` DESC

Answer 2

Sql statements are somewhat evaluated in the following order:

• FROM
• WHERE
• SELECT
• GROUP
• HAVING
• ORDER

So things you define in the SELECT-clause are not available in the WHERE-clause. You would need to put that constraint into a HAVING-clause:

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
HAVING `grade` >= 5
ORDER BY `grade` DESC