SQL - using a value in a nested select

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 t开发者_如何学编程o return this value, thanks!

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);

You can circumvent the problem alltogether by placing the subselect in an INNER JOIN of its own.

SELECT t.iteration
       , t.column
FROM   table t
       INNER JOIN (
         SELECT id, MAX(iteration) AS iteration
         FROM   table
         WHERE  id = 223652
       ) tm ON tm.id = t.id AND tm.iteration = t.iteration

Since you're using Oracle, I'd suggest using analytic functions for this:

SELECT * FROM (
    SELECT col,
      iteration,
      row_number() over (partition by id order by iteration desc) rn
    FROM tab
    WHERE  id = 223652
) WHERE rn = 1

do it like this:

with maxiteration as
(
    SELECT MAX(iteration) AS maxiteration
    FROM   table
    WHERE  id = 223652
)
select
    column, 
    iteration
from
    table
where
    id = 223652
    AND iteration = maxiteration
;

Not 100% sure on Oracle syntax, but isn't it something like:

select iteration, column from table where id = 223652 order by iteration desc limit 1

I would approach this problem in a slightly different way. You're basically looking for the row that has no other iterations greater than it. There are at least 3 ways I can think of to do this:

SELECT
    T1.iteration AS maxiteration,
    T1.column
FROM
    Table T1
WHERE
    T1.id = 223652 AND
    NOT EXISTS
    (
        SELECT *
        FROM Table T2
        WHERE
            T2.id = 223652 AND
            T2.iteration > T1.iteration
    )

Or...

SELECT
    T1.iteration AS maxiteration,
    T1.column
FROM
    Table T1
LEFT OUTER JOIN Table T2 ON
    T2.id = T1.id AND
    T2.iteration > T1.iteration
WHERE
    T1.id = 223652 AND
    T2.id IS NULL

Or...

SELECT
    T1.iteration AS maxiteration,
    T1.column
FROM
    Table T1
INNER JOIN (SELECT id, MAX(iteration) AS maxiteration FROM Table T2 GROUP BY id) SQ ON
    SQ.id = T1.id AND
    SQ.maxiteration = T1.iteration
WHERE
    T1.id = 223652

EDIT: I didn't see the ORA error the first time reading the question and it wasn't tagged as Oracle specific. I think that there may be some differences in the syntax and use of aliases in Oracle, so you may need to tweak some of the above queries.

The Oracle error is telling you that it doesn't know what maxiteration is, because the column alias isn't available yet inside the subquery. You need to refer to it by the table alias and column name instead of the column alias I believe.

You do something like

select maxiteration,column from table a join (select max(iteration) as maxiteration from table where id=1) b using (id) where b.maxiteration=a.iteration;

This could of course return multiple rows for one maxiteration unless your table has a constraint against it.