CODEWITHSUNDEEP
sqldatabaseoracle-database
curious_nustian
curious_nustian

Reputation: 616

using WHERE clause in inline view/derived table

I have query, inspired from qualify in oracle to select unique active users from my table. Query looks like this

SELECT
    COUNT(ID)
FROM
    (
        SELECT  MB.*
                ,ROW_NUMBER ()
                    OVER (PARTITION BY ID ORDER BY registered_date_time DESC)
                LATEST_DATE
        FROM TBL_ID MB
     ) A
WHERE 
    LATEST_DATE = 1
AND ID_STATUS = 'Active' 
AND UPPER(PROFILE_RULE) IN ('Allow 1','Allow 2')

Running this query I get a count. From the perspective of optimizing the query I tried moving the WHERE clause inside the inline view and query looks like this

SELECT
    COUNT(ID)
FROM
    (
        SELECT  MB.*
                ,ROW_NUMBER ()
                    OVER (PARTITION BY ID ORDER BY registered_date_time DESC)
                LATEST_DATE
        FROM TBL_ID MB
        WHERE
                ID_STATUS = 'Active' 
        AND UPPER(PROFILE_RULE) IN ('Allow 1','Allow 2')
     ) A
WHERE 
    LATEST_DATE = 1

But now I get a different count. I assume I am missing any detail or it's the expected behaviour? As per my understanding this should not affect the result dataset. Correct me if wrong.

Upvotes: 0

Views: 140

Answers (1)

Littlefoot
Littlefoot

Reputation: 142733

1st query: you're calculating latest_date for all records in the table. Then you're counting those whose latest_date = 1 and satisfy other where conditions.

2nd query: you're taking rows that satisfy WHERE condition, and calculate latest_date out of that record pool. Then, in the main query, you're counting only rows whose latest_date = 1.

So, why do they differ? Because the 1st query's latest_date doesn't have to belong to active status or specified profile rules, but some other combination(s) of those values. It means that you've already ruled out certain rows from the result.

Upvotes: 3

Related Questions