Distinct vs row_number() – query with same condition giving different result in oracle?

I am trying to eliminate duplicate based on combination of three columns on the same table with some filter conditions through two different approaches but for some reason the result of both are queries are not coming as same.

Query 1:

select count(*) 
from (
    select distinct 
        SERIAL_NBR,
        MAC_ADDR,
        UNIT_ADDR 
    from TGT_DEVICE_DETAILS 
    where MODEL_ID = 'ABC' and REC_CREATE_DT_KEY = 20191130
);

Result – 66181

Query 2:

select count(*) from (
    select 
        xi.*,
        row_number() over (
            partition by xi.SERIAL_NBR,xi.MAC_ADDR,xi.UNIT_ADDR 
            order by test_date_m desc
        ) rownumber 
    from TGT_DEVICE_DETAILS xi
) 
where 
    rownumber  = 1 
    and REC_CREATE_DT_KEY = 20191130 
    and MODEL_ID = 'ABC';

Result – 65495

Shouldn’t the result of both the queries be the same ? Would be great if someone can tell me what am i doing wrong ?

Thanks, Kavin

Answer

The second query has conditions REC_CREATE_DT_KEY = 20191130 and MODEL_ID = 'ABC' in the outer query, while in the first query these are in the inner query.

Move the conditions to the inner query and you should get the same results:

select count(*) from (
    select 
        xi.*,
        row_number() over (
            partition by SERIAL_NBR, MAC_ADDR, UNIT_ADDR 
            order by TEST_DATE_M desc
        ) rownumber 
    from TGT_DEVICE_DETAILS xi
    WHERE REC_CREATE_DT_KEY = 20191130 and MODEL_ID = 'ABC'
) 
where rownumber  = 1;