SQL - using a value in a nested select

Question

Hope the title makes some kind of sense - I'd basically like to do a nested select, based on a value in the original select, like so:

SELECT MAX(iteration) AS maxiteration,
       (SELECT column
        FROM   table
        WHERE  id = 223652
               AND iteration = maxiteration)
FROM   table
WHERE  id = 223652;  

I get an ORA-00904 invalid identifier error.

Would really appreciate any advice on how to return this value, thanks!

Answer 1

It looks like this should be rewritten with a where clause:

select iteration,
       col
from tbl
where id = 223652
and iteration = (select max(iteration) from tbl where id = 223652);