Skip to content
Advertisement

SQL select the latest record on joined tables

I want to retrieve the property which an user paid a tax last.

have two tables person_properties and property_taxes

person_properties
---------
person_id
property_id

property_taxes
---------
property_id
year

What I tried so far:

SELECT pp.person_id, MAX(pt.year) 
FROM property_taxes pt
JOIN person_properties pp ON pt.property_id = pp.property_id
GROUP BY pp.person_id

This gives me the person_id along the max year. But I actually need the property_id, the property which an user paid last.

If I replace pp.person_id by pp.property_id, I then get all properties, of course.. not grouped by person, which excludes an user that has more than one property.

Any little tweak that can help me get the desired result?

Advertisement

Answer

DISTINCT ON might do what you want?

SELECT DISTINCT ON (pp.person_id)
       *
       FROM property_taxes pt
            INNER JOIN person_properties pp
                       ON pp.property_id = pt.property_id
       ORDER BY pp.person_id ASC,
                pt.year DESC;
6 People found this is helpful
Advertisement