reference to temporary relation from qualification list

Question

Consider an SQL query like this

SELECT Temp.rating, Temp.avgage
FROM (SELECT S.rating, AVG (S.age) AS avgage
FROM Sailors S 
GROUP BY S.rating) AS Temp
WHERE Temp.avgage = (SELECT MIN (Temp.avgage)
FROM Temp)

MySQL monitor gives this error:

ERROR 1146 (42S02): Table 'testDB.Temp' doesn't exist

What should I do to avoid this error?

Answer 1

SELECT Temp.rating, Temp.avgage 
FROM 
  ( SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S 
    GROUP BY S.rating
  ) AS Temp
WHERE Temp.avgage = 
      ( SELECT MIN (Temp.avgage)
        FROM Temp                --- the error is here
      )

---

Depending on how you want to handle ties, you could just use:

SELECT Temp.rating, Temp.avgage 
FROM 
  ( SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S 
    GROUP BY S.rating
  ) AS Temp
ORDER BY Temp.avgage 
LIMIT 1

or the simpler equivalent:

SELECT S.rating, AVG(S.age) AS avgage
FROM Sailors S 
GROUP BY S.rating
ORDER BY avgage 
LIMIT 1

---

or this one (that shows all tied results):

SELECT Temp.rating, Temp.avgage 
FROM 
  ( SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S 
    GROUP BY S.rating
  ) AS Temp
WHERE Temp.avgage = 
      ( SELECT AVG(S.age) AS avgage
        FROM Sailors S 
        GROUP BY S.rating
        ORDER BY avgage 
        LIMIT 1              
      )