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?
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
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.
Actually i want all records for the greatest end dt an di already tried this but thisgives an error in syntax.
select * from B qualify end_date = max(end_dt) over ()
but check also
select * from b where (select max(end_date) from b) = end_date
depend on your data which will be faster
I think this will not give the desire result.