query to pick out the most recent address

[neemi]

I have two tables, Address_History and Address_detail.

Fields in Address_History are:
Applicant_ID
Address_ID
Address_Start_Date
Address_End_Date

Fields in Address_Detail are:
Address_ID
Address_Line1
Address_Line2
Address_Line3
Address_Line4
Address_Line5
County
Postcode_Area
Postcode_sector

I was wondoring if someone can help me with constructing the best way to basically pick out the most recent address someone lives at (Max Address_Start_Date).

I know I would need to find the maximum Address_Start_Date from Address_History, get the Address_ID from there, and get the address details from the corresponding table using this Address_ID.

This would also be determined by a parameter called Applicant_ID, which is passed in to determine whose address I am retriving.

would this all be a query or would part of it be pl/sql.

Help with this appreciated.

Cheers,
Neemi

 

[nagornyi]

select
  Address_Line1
, Address_Line2
, Address_Line3
, Address_Line4
, Address_Line5
, County
, Postcode_Area
, Postcode_sector
from
  Address_detail a
, (select Address_ID, max(Address_Start_Date) 
   from Address_History
   where Applicant_ID = :p_Applicant_ID
   group by Address_ID) b
where a.Address_ID = b.Address_ID