CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
user881703
user881703

Reputation: 1199

query optimization (nested subqueries)

I try to simplify below subqueries to improve select statement. I have table with 3 basic columns as ID, GRAGE and AGE. To select all records which have GRADE same as GRADE of Maximum ID Might somebody have better way that create nested subqueries, welcome all your suggestions?

Note: My apologise for formatting the table

ID    GRADE     AGE
10      A           30
12      B           45
13      A           15
09      B           14
20      A           12

SELECT
    *
FROM
    TABLE
WHERE
    GRADE = (
        SELECT
            grade
        FROM
            TABLE
        WHERE
            id = (SELECT MAX(id) FROM TABLE)
    );

Upvotes: 0

Views: 82

Answers (2)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You can do the following (not much simpler though):

SELECT
    *
FROM
    TABLE
WHERE
    GRADE IN (
        SELECT
            first_value (GRADE) over (ORDER BY id DESC)
        FROM
            TABLE
    )

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You could use a CTE to make the query easier to read:

WITH cte AS
(
    SELECT GRADE,
           ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID DESC) RowNum
    FROM yourTable
)

SELECT *
FROM yourTable
WHERE GRADE = (SELECT t.GRADE FROM cte t WHERE t.RowNum = 1)

However, I don't have a problem with your original approach because the subqueries are not correlated to anything. What I mean by this is that

SELECT MAX(id) FROM yourTable

should effectively only be executed once, and afterwards sort of be treated as a constant. Similarly, the query

SELECT grade FROM TABLE WHERE id = (max from above query)

should also be executed only once. This assumes that the query optimizer is smart enough to figure this out, which it probably is.

Upvotes: 1

Related Questions