Fetching fetch the first occurrence from the result

I have an oracle sql query

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 what the problem is is that 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 occurrence of row.

Answers


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".


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

Need Your Help

Make turn.js use a class insted of id

javascript jquery

i want to make turn.js use class insted on id the below fiddle uses id so am trying with a class

Android-programmatically bypass remove default lock screen

android launcher

I've defined a default application as a launcher with a HOME intent so it always runs when I boot the device.