Search code examples
sqloracle11ginner-join

Query to join left table with latest record on the right table using inner join

Below are the two tables of an oracle database(11g):

Table 1: EXPORT_TABLE

Insert into EXPORT_TABLE (INTBA,INDEXNAME,BANAME,SCBA,NAME,NVRCOUNTRY) values ('133520', 'Hong Kong',659923,'0',' Trustee Limited','HK');

Insert into EXPORT_TABLE (INTBA,INDEXNAME,BANAME,SCBA,NAME,NVRCOUNTRY) values ('133516',' Holdings ',659923,'0',' Banking Holdings','CH');

Table 2 : ba_name

Insert into ba_name (MAINKEY,INTBA,CLIENTNAME,BASEQ,TRAN_DATE,name_type_code,indx_name_e_dte) values (1000,'133520','ROYAL Challenge', 0, '2016-MAR-10' ,'001',NULL);

Insert into ba_name (MAINKEY,INTBA,CLIENTNAME,BASEQ,TRAN_DATE,name_type_code,indx_name_e_dte) values (1001,'133520','Royal Challenge',0, '2016-SEPT-24','001',NULL);

Insert into ba_name (MAINKEY,INTBA,CLIENTNAME,BASEQ,TRAN_DATE,name_type_code,indx_name_e_dte) values (1003, '133516','Deloitte AG','0','2016-MAY-20','001',NULL);

The requirement requires to join EXPORT_TABLE with ba_name on a join condition mentioned below 

          ba_name.INTBA = EXPORT_TABLE.INTBA
          AND ba_name.name_type_code = '0001'
          AND ba_name.indx_name_e_dte IS NULL

In the second table 'ba_name' , INTBA is not a unique key and the second table 'ba_name ' can have multiple rows of same INTBA which can occur on different transaction dates. Its a legacy system(cantdo anything now)

Since we have duplicate entries with INTBA, we need to join EXPORT_TABLE (column name=INTBA) with that of ba_name table , which is having the latest TRAN_DATE.

In other words i would like to join the EXPORT_TABLE with the matching record on ba_name which is very recent or having latest TRAN_DATE date. I wnat to join INTBA '133520' OF EXPORT_TABLE with INTBA '133520' of ba_name having tran_date as '2016-SEPT-24' , not the one having MARCH as tran_date ('2016-MAR-10').

THE RESULT SET WANTED TO SEE IS :


                             (intBA |CLIENTNAME| BASEQ | TRAN_DATE)
                             ======================================

                             133520 |'Royal Challenge'| 0  | '2016-SEPT-24'
                             133516 |'Deloitte AG'    |'0' | '2016-MAY-20'

However tried with below query :

SELECT   EXPORT_TABLE.intBA, 
         ba_name.CLIENTNAME,
         ba_name.BASEQ,             
         ba_name.TRAN_DATE

         FROM EXPORT_TABLE   INNER JOIN  ba_name

       ON ba_name.INTBA = EXPORT_TABLE.INTBA

              AND ba_name.name_type_code = '0001'
              AND ba_name.indx_name_e_dte IS NULL

I got below result which is incorrect:


                             (intBA |CLIENTNAME| BASEQ | TRAN_DATE)
                             ======================================

                             133520 |'Royal Challenge'| 0  | '2016-SEPT-24'
                             133520 |'RoYAL Challenge'| 0  | '2016-MAR-10'
                             133516 |'Deloitte AG'    | 0 | '2016-MAY-20'

Can you suggest how can I join left side table with the matching row of second(right) table having the latest transaction date.

Solution

  • One common method is row_number():

    SELECT e.intBA, n.CLIENTNAME, n.BASEQ, n.TRAN_DATE
FROM EXPORT_TABLE e INNER JOIN
     (SELECT n.*,
             ROW_NUMBER() OVER (PARTITION BY n.INTBA ORDER BY n.TRAN_DATE DESC) as seqnum
      FROM ba_name n
      WHERE n.name_type_code = '0001' AND n.indx_name_e_dte IS NULL
     ) n
     ON n.INTBA = e.INTBA AND n.seqnum = 1;