Can I reuse the result of a subquery to optimize the overall query?

Question

I am using below query to fetch the top two records for profile_run_key. I am using three almost similar queries to get this done. This means I am traversing the table thrice for the "where" clause. So I think this will take 3(n) time for execution. Alternatively I can use "Order by" but the it will take nlogn time to execute.

SELECT name, weighted_average
      FROM idp_weighted_avg 
           where (profile_run_key =
                   (SELECT MAX (profile_run_key)
                      FROM idp_weighted_avg
                     WHERE SCORECARD_IDENTIFIER = 'U:D8yIYvW6EeGKyklcM7Co1A')
                OR profile_run_key =
                      (SELECT MAX (profile_run_key)
                         FROM idp_weighted_avg
                        WHERE SCORECARD_IDENTIFIER = 'U:D8yIYvW6EeGKyklcM7Co1A'
                              AND profile_run_key <
                                     (SELECT MAX (profile_run_key)
                                        FROM idp_weighted_avg
                                       WHERE SCORECARD_IDENTIFIER =
                                                'U:D8yIYvW6EeGKyklcM7Co1A')))

I was wondering if I can reuse (I don't want to create a temp table) the result of the below sub query? Any alternatives? Sugestions?

SELECT MAX (profile_run_key)
              FROM idp_weighted_avg
             WHERE SCORECARD_IDENTIFIER = 'U:D8yIYvW6EeGKyklcM7Co1A'

Oracle. But I want it to be generic enough to run on Oracle, DB2 and SQLServer atleast. — user1590695, Sep 06 '12 at 10:58

score 0 · Accepted Answer · answered Sep 06 '12 at 13:39

It seems like you are selecting the two largest records for a given SCORECARD_IDENTIFIER. If that's what you realy are after, you can add

a ROW_NUMBER
PARTITION BY SCORECARD_IDENTIFIER - Restarts the rownumber for each group
ORDER BY profile_run_key DESC - Numbers the key in a group from hig to low

SQL Statement

SELECT  *
FROM    idp_weighted_avg wa
        INNER JOIN (
          SELECT  profile_run_key
                  , rn = ROW_NUMBER() OVER (PARTITION BY SCORECARD_IDENTIFIER ORDER BY profile_run_key DESC)
          FROM    idp_weighted_avg
          WHERE   SCORECARD_IDENTIFIER = 'U:D8yIYvW6EeGKyklcM7Co1A'
        ) rn ON rn.profile_run_key = wa.profile_run_key
WHERE   rn <= 2

@user1590695 - You are welcome. If this answered your question, you can mark it as such. — Lieven Keersmaekers, Sep 09 '12 at 16:44

Can I reuse the result of a subquery to optimize the overall query?

1 Answers1