How to apply RANK() only if the JOIN condition returns a matching record?

Question

I am struggling with an Oracle SQL query where I want to apply ranks only if there is a matching record. Here's my data:-

Table 1:
ID State_Code
1   NY
2   DC
3   AL

Table 2:
ID EXPIRY_DATE STATE_CODE X_CODE
1   30-DEC-21     NY        ABC
1   30-JUN-21     NY        XYZ
2   30-DEC-21     DC        SQL
2   30-JUN-21     AZ        DEF
3   30-JUN-21     AK        PQR

RESULT:
ID STATE_CODE  X_CODE
1    NY         ABC
2    DC         SQL
3    AL         (null)

I want to join the states codes based on ID. If more than one match, then choose/rank the records based on Expiry_Dates.

Answer 1

If you want columns from both tables, then this is a handy place for a lateral join. Based on your description, you want:

select t1.*, t2.x_code
from table1 t1 cross join lateral
     (select t2.*
      from table2 t2
      where t2.id = t1.id
      order by expiry_date desc
      fetch first 1 row only
     ) t2;

Based on your sample data, you seem to want the match on both id and state_code:

select t1.*, t2.x_code
from table1 t1 left join lateral
     (select t2.*
      from table2 t2
      where t2.id = t1.id and t2.state_code = t1.state_code
      order by expiry_date desc
      fetch first 1 row only
     ) t2
     on 1=1;

Here is a db<>fiddle.