Need to transfer certain records of some columns from Table1 to Table2 but filtering rows based on condition. Lets say Table1 looks like as shown below, has many columns in it. In Table2, need to insert rows from Table1 based on following: First, select A, B, C, D, E, F from Table1 where D='xyz' and E=1; and on the result

Oracle SQL: Transfer certain records from one table to another filtering rows based on condition

Need to transfer certain records of some columns from Table1 to Table2 but filtering rows based on condition.

Lets say Table1 looks like as shown below, has many columns in it.

Table1

A                 B                        C        D     E    F       G     H   ...
1    24-OCT-20 08.22.57.642000000 AM      100      xyz    1    1     
2    24-OCT-20 08.22.57.642000000 AM      100      xyz    1    0
13   25-OCT-20 05.47.52.733000000 PM      100      xyz    1    0
34   26-OCT-20 09.22.57.642000000 AM      100      xyz    1    0
25   26-OCT-20 09.25.57.642000000 AM      101      xyz    1    0
26   26-OCT-20 09.25.57.642000000 AM      101      xyz    1    1
6    26-OCT-20 09.25.57.642000000 AM      101      abc    1    1
10   26-OCT-20 09.25.57.642000000 AM      101      xyz    0    1
17   26-OCT-20 04.22.57.642000000 AM      100      xyz    1    0
18   26-OCT-20 06.22.57.642000000 AM      105      xyz    1    1
19   26-OCT-20 06.22.57.642000000 AM      105      xyz    1    0

In Table2, need to insert rows from Table1 based on following:

First, select A, B, C, D, E, F from Table1 where D='xyz' and E=1; and on the result of this query apply the following condition to further filter out unwanted rows:

Condition: For same values in columns B, C, D & E in 2 different rows, if column F has 2 different values then only keep the row with greater value in column A.

So desired output in Table2 is shown as below:

Table2

A                 B                        C        D     E    F
2    24-OCT-20 08.22.57.642000000 AM      100      xyz    1    0
13   25-OCT-20 05.47.52.733000000 PM      100      xyz    1    0
34   26-OCT-20 09.22.57.642000000 AM      100      xyz    1    0
26   26-OCT-20 09.25.57.642000000 AM      101      xyz    1    1
17   26-OCT-20 04.22.57.642000000 AM      100      xyz    1    0
19   26-OCT-20 06.22.57.642000000 AM      105      xyz    1    0

How can this be achieved through the simplest and most efficient SQL query?

Any help will be appreciated.

Answer

You can use window functions:

insert into table2 (a, b, c, d, e, f)
select a, b, c, d, e, f
from (
    select t1.*,
        row_number() over(partition by b, c, d, e order by a desc) rn
    from table1 t1
    where d = 'xyz' and e = 1
) t1
where rn = 1

Advertisement

Answer