AnsweredAssumed Answered

How to fetch First or Last record while connecting to databases and using a left outer join to join the data?

Question asked by amitkumar.chouksey on Jun 14, 2018
Latest reply on Jun 15, 2018 by trey.yeager826393

Hi All,

 

I have a scenario where I have fetch data from two tables in same database using left outer join. However while joining data the requirement is to fetch first or last record from second table as following :

 

Table1 :

 

ID    NAME

1      Amit

2      Manoj

3      Aswhini

 

 

Table2:

ID   NAME   EMAIL

1     Amit      amit11@xyz.com

2     Amit      amit22@xyz.com

2     Manoj   Manoj 11@xyz.com

3     Ashwini Ashwini11@xyz.com

 

Left outer join with First match Output should be :

 

ID   NAME   EMAIL

1     Amit      amit11@xyz.com

2     Manoj   Manoj 11@xyz.com

3     Ashwini Ashwini11@xyz.com

 

Left outer join with last match Output should be :

 

ID   NAME   EMAIL

1     Amit      amit22@xyz.com

2     Manoj   Manoj 11@xyz.com

3     Ashwini Ashwini11@xyz.com

 

I tried using document cache in Map but it is always picking the amit11@xyz.com email address.

 

However we tried using a complex query as well, but result was achievable only through using Rownum, which is difficult while having large sets of data.

 

This whole scenario is also a problem while connecting to two different database where a complex query CAN'T be written.

 

Could anyone please suggest this to achieve both the scenarios.

 

Many Thanks in Advance

Outcomes