Minus query with group by clause giving different result

I have checked various similar questions but could not find the answer.
I have 2 same tables(table1 and table2) but it gets loaded at different time, once in the morning and other at the night.
I did minus query on these:-

select data1,data2,data3,
    file_dt from table1 minus select data1,data2,data3,
    file_dt from table2.

select data1,data2,data3,
    file_dt from table2 minus 
select data1,data2,data3,
    file_dt from table1

and get the 0 records which is desired results.
But due to parallel loading, I need to fetch unique records from table1 and table2.
I tried both rowid and rownumber approach still getting records in minus query.
Approach1(rowid):-

select data1,data2,data3,
    file_dt from table1
 where rowid IN ( select rid
                    from (select rowid rid, 
                                 row_number() over (partition by 
                         data2,data3
                                   order by file_dt DESC) rn
                            from table1)
                   where rn = 1)
minus
select data1,data2,data3,
    file_dt from table2
 where rowid IN ( select rid
                    from (select rowid rid, 
                                 row_number() over (partition by 
                         data2,data3
                                   order by file_dt DESC) rn
                            from table2)
                   where rn = 1)

I get 2 records but expect 0 records.
Approach2(row_number):-

select data1,data2,data3,
        file_dt,
       ROW_NUMBER() OVER(PARTITION BY data2,data3
                         ORDER BY file_dt desc) rn        
from table1)
WHERE rn = 1
minus
select data1,data2,data3,
        file_dt,
       ROW_NUMBER() OVER(PARTITION BY data2,data3
                         ORDER BY file_dt desc) rn        
from table2)
WHERE rn = 1

Again get some records.
Any help is appreciated!

> I did minus query on these and get the 0 records which is desired results

Assuming you did something like:

select data1, data2, data3, file_dt from table1
minus
select data1, data2, data3, file_dt from table2

that means there is no data in table1 which does not also appear in table2.

It does not mean the tables are identical - there could be data in table2 which does not appear in table1. The minus operation only goes one way - from the docs, "All distinct rows selected by the first query but not the second".

If table2 has additional data for the same data1/2/3 combinations but with later dates then those won't be seen by that basic minus query. But when you change the query to be based on

row_number() over (partition by data2,data3 order by file_dt DESC) as rn

then both subqueries only get the most recent row (based on file_dt) for the combination of data2/3 (but not data1, which looks odd but doesn't matter at this point).

That means the second subquery will pick up records that are newer than those in the first subquery.

To demonstrate with a simple example, if you have this data in table1:

and this in table2:

All three rows in the first table also appear in the second table, so when you minus them no data is returned.

If you look at the rn calculation for both tables:

select data1, data2, data3, file_dt,
  row_number() over (partition by data2,data3 order by file_dt DESC) as rn
from table1

select data1, data2, data3, file_dt
from (
  select data1, data2, data3, file_dt,
    row_number() over (partition by data2,data3 order by file_dt DESC) as rn
  from table1
)
where rn = 1

select data1, data2, data3, file_dt,
  row_number() over (partition by data2,data3 order by file_dt DESC) as rn
from table2

select data1, data2, data3, file_dt
from (
  select data1, data2, data3, file_dt,
    row_number() over (partition by data2,data3 order by file_dt DESC) as rn
  from table2
)
where rn = 1

you can see that filtering on rn = 1 returns different rows for the two subqueries. When you effectively then do:

| 1 | 2 | 3 | 01-JAN-23 |
| 2 | 3 | 4 | 02-JAN-23 |
| 3 | 4 | 5 | 03-JAN-23 |

MINUS

| 1 | 2 | 3 | 01-JAN-23 |
| 2 | 3 | 4 | 04-JAN-23 |
| 3 | 4 | 5 | 05-JAN-23 |

the only common row is now

| 1 | 2 | 3 | 01-JAN-23 |

so only that row is elminated by the minus operation, and your query returns the other two:

fiddle

This is how I see this:

You select

select data1, data2, data3, file_dt from table1
minus
select data1, data2, data3, file_dt from table2

and get no rows. Then you do the same, but only look at the latest entry per data2, data3 in the tables

select data1, data2, data3, file_dt from <latest data2/data3 rows in table1>
minus
select data1, data2, data3, file_dt from latest data2/data3 rows in table2>

and suddenly you get rows.

That simply means

• that table2 contains every row that is in table1 (first query)
• but table2 also contains additional (newer) rows that don't exist in table1. This leads to the newest row in table1 not having a match with the newest row in table2. (second query)

This looks correct. You say that the tables get their data at different times, so one table has newer information, and if you only compare the newest information, then you'll have differences (at least the datetime will be different).

Well, now that I posted my answer, stackoverflow suddenly shows two other answers posted much earlier. So I guess my answer won't add much to the case.

It's a bit unclear what you're after. You say "I need to fetch unique records from table1 and table2" but then say you expect 0 rows. Why would you get 0 rows when trying to get unique records?

Setting aside the 0-rows thing then, if your need is to get unique records from the tables, does this work for you? Combine the two tables with UNION ALL, then compute your ROW_NUMBER of the combined set, then select your desired row.

select data1,data2,data3,file_dt
  from (select data1,data2,data3,file_dt,
               ROW_NUMBER() OVER (PARTITION BY data2,data3 ORDER BY file_dt DESC) seq
          from (select data1,data2,data3,file_dt from table1
                union all
                select data1,data2,data3,file_dt from table2))
 where seq = 1