SQL select the latest record on joined tables

Question

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?

is there only 'year' as time column? If it is, how do you plan finding latest one? there must be multiple property in the same year — berkancetin, Dec 19 '19 at 20:13
look [here](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) for some reference — Anatoliy R, Dec 19 '19 at 20:24
@berkancetin nice question, its an anual tax, and there is a constraint in the DB so it wont happen. — queroga_vqz, Dec 19 '19 at 20:33

sticky bit · Accepted Answer · 2019-12-19T20:29:16.670

4

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;

edited Dec 19 '19 at 20:29

answered Dec 19 '19 at 20:21

sticky bit

36,626
12
31
42

@queroga_vqz . . . This does not just "work as well". This is the best way to solve this problem in Postgres and it should be the accepted answer. – Gordon Linoff Dec 19 '19 at 20:40

score 1 · Answer 2 · answered Dec 19 '19 at 20:17

something like this

SELECT
    pt.property_id,
    pt.year
FROM
    property_taxes pt,
    person_properties pp,
    (
        SELECT
            pp.person_id p,
            MAX(pt.year) y,
        FROM
            property_taxes pt
        JOIN person_properties pp ON pt.property_id = pp.property_id
        GROUP BY pp.person_id
    ) sub
WHERE
    pt.property_id=pp.property_id AND
    pp.person_id=sub.p AND
    pt.year=sub.y

SQL select the latest record on joined tables

2 Answers2