I have 2 tables as seen below:
Now the question is :
How can I have a view which shows the details of the last Owner? in other words I need the details of person who has MAX(StartDate) in tbl_Owners table?
I want to find the latest owner of each apartment.
I tried different approaches but I couldn't find the way to do that.
I know I need to get the personID in a Group By clause which groups records by AppID but I can't do that
select t1.* from tbl_persons as t1 inner join ( select t1.* from tbl_owners as t1 inner join ( select appid,max(startdate) as startdate from tbl_owners group by appid ) as t2 on t1.appid=t2.appid and t1.startdate=t2.startdate ) as t2 on t1.personid=t2.personid