All Forums Database
DeepakAsule 8 posts Joined 07/15
30 Jul 2015
HOw to join on nearest lower value in teradata sql

I have two tables in teradata 
table1
1
2
3
5
 
table 2
2
3
4
6
 
output
 
2 2 
3 3
4 3
6 5
 
if matcing value found col should join on matching value else nearest lower value.
 
Please help me 
 
I have to implement this scenario,,,It's urgent
 
Thanks in advance

dnoeth 4628 posts Joined 11/04
30 Jul 2015

Writing the join-condition isn't that hard, but performance will be horrible :-)
What's your Teradata release and the number of rows in those tables?
Are those columns unique?

Dieter

DeepakAsule 8 posts Joined 07/15
30 Jul 2015

I am using teradata 14
I am having arround 2005527 rows in a table
 
I have to apply this condition in ON keyword
 
Thanks, 
Deepak
 

DeepakAsule 8 posts Joined 07/15
30 Jul 2015

in other table I have 1916 record and I have to join this table to first one table.
 
I have applied other conditions as well in ON cluase like t1.id = t2.id
 
But I have to apply nearest matching condition as well on another column

DeepakAsule 8 posts Joined 07/15
30 Jul 2015

Can you post the join condition ?

DeepakAsule 8 posts Joined 07/15
30 Jul 2015

sorry 
 
I have 425709 records in first table and 1916 records in second table.
 
Deepak

 

dnoeth 4628 posts Joined 11/04
30 Jul 2015

Hi Deepak,
the join is like this:

select t2.*, t1.*
from table2 as t2
join table1 as t1
  on t1.i = (select max(t3.i) from table1 as t3 where t3.i <=t2.i)

But this result in a product join, which might be ok if you got another joiin condition.
 
I prefer following approach:
UNION both columns, find the last value using an OLAP function and then join back to both tables:

select  v2,
   max(v1) 
   over (order by coalesce(v1,v2), v2
         rows unbounded preceding) as newV1
from
 (
   select i as v1, null as v2 from table1
   union
   select null as v1, i as v2 from table2
 ) as dt
qualify v2 is not null

Put this in a Derived Table and join back:

select t1.*, t2.*
from table1 as t1 join (previous_query) as dt
  on t1.i = dt.newv1
join table2 as t2
  on dt.v2 = t2.i

Depending on the actual data this might be much more efficient...

Dieter

You must sign in to leave a comment.