Odtnhj

I have found many solutions to selecting the most recent date, however, I have not been able to successfully include all of my columns when I do this because I have data that is unique that I wish to include.

Here is my original query:

select p.scode, u.scode, sq.dsqft0, sq.dtdate
from unit u
join property p on p.hmy = u.hproperty
join sqft sq on sq.hpointer = u.hmy
where p.hmy = 19

This returns:

p.scode u.scode dsqft0 dtdate
-------------------------------------------------------------------
01200100 100 23879 1/1/1980 12:00:00 AM
01200100 100 19000 10/30/2017 12:00:00 AM
01200100 100 23879 11/1/2018 12:00:00 AM
01200100 200 33854 1/1/1980 12:00:00 AM
01200100 400 7056 1/1/1980 12:00:00 AM
01200100 400 12056 6/1/2015 12:00:00 AM

I'd like to receive only the most recent entry for each p.scode and u.scode, however, I don't want to group by sq.dsqft0 - I just want to receive back whatever is in that column for the most recent p.scode/u.scode row.

If I remove the sq.dsqft0 column from my query, I'm able to narrow down to the rows I want, but I need the information in the sq.dsqft0 column. Here is the query that almost works:

select p.scode,u.scode,max(sq.dtdate)as currentdate
from unit u
join property p on p.hmy=u.hproperty
join sqft sq on sq.hpointer=u.hmy
where p.hmy=19
group by p.scode,u.scode

This returns:

p.scode u.scode currentdate
01200100 100 11/1/2018 12:00:00 AM
01200100 200 1/1/1980 12:00:00 AM
01200100 400 6/1/2015 12:00:00 AM

These are the correct rows, however, if I include sq.dsqft0, I have to include it in the group by statement and it returns additional rows where the sq.dsqft0 column does not match. If I enter this:

select p.scode,u.scode,sq.dsqft0,max(sq.dtdate) as currentdate
from unit u
join property p on p.hmy=u.hproperty
join sqft sq on sq.hpointer=u.hmy
where p.hmy=19
group by p.scode,u.scode,sq.dsqft0

It returns this:

p.scode u.scode dsqft0 dtdate
01200100 100 19000 10/30/2017 12:00:00 AM
01200100 100 23879 11/1/2018 12:00:00 AM
01200100 200 33854 1/1/1980 12:00:00 AM
01200100 400 7056 1/1/1980 12:00:00 AM
01200100 400 12056 6/1/2015 12:00:00 AM

my suggestion is to use subquery to find the most recent data and then join with original table to get "dsqft0":

select * from 
 ( select p.scode,u.scode as uscode, current_date, dsqft0 
 from unit u
 join property p on p.hmy=u.hproperty
 join sqft sq on sq.hpointer=u.hmy
 where p.hmy=19 ) a
 join 
 (select p.scode,u.scode as uscode ,max(sq.dtdate)as currentdate
 from unit u
 join property p on p.hmy=u.hproperty
 join sqft sq on sq.hpointer=u.hmy
 where p.hmy=19 
 group by p.scode,u.scode ) sub 
 on a.scode = sub.scode
 and a.uscode = sub.uscode
 and a.current_date = sub_current_date