translate join on two conditions to a select statement

Database
Enthusiast

Re: translate join on two conditions to a select statement

Thanks everyone.

What if I want to get the records from B which qualify this condition

QUALIFY RANK() OVER (ORDER BY End_dt DESC) =1.

If i add it to the end of the query the left outer join loses its meaning. how can i put it in the join condition?

Senior Supporter

Re: translate join on two conditions to a select statement

do you really mean RANK? Are you aware that RANK can result in many rows per value?

Also this would give all records for the biggest end_dt. So you might need to consider to add a partition clause.

So this might work

select *

from A

       left outer join

       (select * from B qualify row_number() over (partition by empid, deptid order by end_dt desc) = 1) as B

           ON "join condition"

where A.comp_id in (2,3)

but this shouldn't be the standard access on table B. You might review the table design in case this would be the standard access.

Enthusiast

Re: translate join on two conditions to a select statement

Actually i want all records for the greatest end dt an di already tried this but thisgives an error in syntax.

Senior Supporter

Re: translate join on two conditions to a select statement

select * from B qualify end_date = max(end_dt) over ()

should do.

but check also

select * from b where  (select max(end_date) from b) = end_date

depend on your data which will be faster

Teradata Employee

Re: translate join on two conditions to a select statement

Here you go mate.

Select ID,Rep_date

from emp

qualify row_number() over (partition by id order by rep_date asc) = 1

Enthusiast

Re: translate join on two conditions to a select statement

I think this will not give the desire result.

Select ID,Rep_date

from emp

qualify row_number() over (partition by id order by rep_date asc) = 1

Becoz if the table has below entires:-

         ID    rep_date

-----------  ----------

          1  2012-01-01

          1  2012-01-02

          1  2012-01-03

          2  2012-01-04

          2  2012-01-05

          2  2012-01-06

          3  2012-01-07

          3  2012-01-08

 

On Running above query the result would be as below:-

         ID    rep_date

-----------  ----------

          1  2012-01-01

          2  2012-01-04

          3  2012-01-07

 

Which is not the desired result

but the below query would solve the purpose

select * from b where  (select max(end_date) from b) = end_date

         ID    rep_date

-----------  ----------

          3  2012-01-08