Using table from select result in its own nested select statement during join in oracle sql develope-CodePudding

This might be a duplicate or poor description of the problem, sorry if so. Is it possible to use sub from the following query in a nested select statement in the join as shown below:

SELECT 
    *
FROM
    (
        SELECT
            several_values_from_several_tables
        FROM
            table1 t1
            JOIN multiple_tables ON t1_and_eachother
        WHERE
            several_conditions_including_nested_select
        ORDER BY
            multiple_columns
    ) sub
INNER JOIN (
    SELECT
        some_id, MAX(some_time_value) AS max_time
    FROM
        sub
    GROUP BY
        some_id
) sub2 ON sub.some_id = sub2.some_id
WHERE
    sub.time = sub2.time

I'd like to use sub in the joins select statement to avoid having to repeat the same select statement as it is quite large and expensive (I also have a second join on a different timestamp which would result in doing the same expensive query 3 times). I guess that sub is not created until joins are performed and where clause is to be executed. If anyone has found a solution or workaround to achieve the same result in a single query before I'd greatly appreciate some pointers.

CodePudding user response：

CTE (a Common Table Expression, a.k.a. the WITH factoring clause) can be used for that, e.g.

WITH
   sub
   AS
      (  SELECT several_values_from_several_tables
           FROM table1 t1 JOIN multiple_tables ON t1_and_eachother
          WHERE several_conditions_including_nested_select
       ORDER BY multiple_columns),
   sub2
   AS
      (  SELECT some_id, MAX (some_time_value) AS max_time
           FROM sub
       GROUP BY some_id)
SELECT *
  FROM sub s JOIN sub2 s2 ON s.some_id = s2.some_id
 WHERE s.time = s2.time

CodePudding user response：

In this case, you should avoid the self-join and use an analytic function:

SELECT *
FROM   (
  SELECT several_values_from_several_tables,
         MAX(some_time_value) OVER (PARTITION BY some_id) AS max_time
  FROM   table1 t1
         JOIN multiple_tables ON t1_and_eachother
  WHERE  several_conditions_including_nested_select
  ORDER BY
         multiple_columns
)
WHERE  some_time_value = max_time

Note: You could also use the RANK or DENSE_RANK analytic functions instead of MAX.