HOw to join on nearest lower value in teradata sql

Database
Enthusiast

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

6 REPLIES
Senior Apprentice

Re: HOw to join on nearest lower value in teradata sql

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?

Enthusiast

Re: HOw to join on nearest lower value in teradata sql

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

Enthusiast

Re: HOw to join on nearest lower value in teradata sql

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

Enthusiast

Re: HOw to join on nearest lower value in teradata sql

Can you post the join condition ?

Enthusiast

Re: HOw to join on nearest lower value in teradata sql

sorry 

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

Deepak


Senior Apprentice

Re: HOw to join on nearest lower value in teradata sql

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