Geek asks and answers

SQL - using value in nested element

I hope the name makes some sense - I would like to make a nested selection based on the value in the original select, for example:

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

I get the wrong identifier error ORA-00904.

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

+3
source share
7 answers

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);
+3
source

You can get around the alltogether issue by putting the subtitle in INNER JOINyourself.

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
+2
source

Oracle, :

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

:

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

Oracle 100%, :

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

. , . 3 , :

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
    )

...

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

...

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

: ORA , , Oracle. , Oracle, .

Oracle , , maxiteration, . , , , .

0

-

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 can, of course, return multiple rows for a single maximization if your table has no limits.

0
source

More articles:


All Articles