mohammad boluki mohammad boluki - 4 months ago 9
SQL Question

How can I fix my GROUP BY clause

I have 2 tables as seen below:
enter image description here

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

Thank you

Answer

Try this

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 
Comments