Fetching fetch the first occurance from the result.

Question

Hello, I have a oracle sql

select
    distinct 
    tab1.col1,
    tab2.col1
from 
    table1 tab1 
    join table2 tab2 on tab1.col1 = tab2.col1

Here i get the as expected in terms of distinct values.

For Example : The result rows are

1  2 
3  4
5  6

Now i want to add one more join for table3. so my sql is

select
    distinct 
    tab1.col1,
    tab2.col1,
    tab3.col1
from 
    table1 tab1 
    join table2 tab2 on tab1.col1 = tab2.col1
    join table3 tab3 on tab1.col1 = tab3.col1

Here whats is the problem is table 3 is returning more than one value. which is resulting in duplicate rows based on table3.

For Example : The result rows are
1  2  4 
1  2  5 
3  4  1
3  4  2
5  6  3

(Here if you notice row 1 & 2 are duplicate and 3 & 4 are duplicate) What i am trying to do is for the join of table3 i want to fetch the first occurance of row.

Please help!

Answer 1

select
    distinct 
    tab1.col1,
    tab2.col1,
    t3.col1
from 
    table1 tab1 
    join table2 tab2 on tab1.col1 = tab2.col1
    join (select distinct col1 from table3) t3 on tab1.col1 = t3.col1

Answer 2

This shud work for you !

select 
    distinct  
    tab1.col1, 
    tab2.col1, 
    MIN(tab3.col1)
from  
    table1 tab1  
    join table2 tab2 on tab1.col1 = tab2.col1 
    join table3 tab3 on tab1.col1 = tab3.col1 
GROUP BY tab1.col1, tab2.col1

Edit: Thoughts,

I am assuming column 3 to be a integer which ever increasing, in that case this works. You can use the date column to define your aggregate accurately to get the "first occurance of your row".