Using this SO answer I've created the following query:
select
created, property_id, requesting_user_id, type, response
from
pdr t1
where
t1.created = (
select max(created)
from pdr t2
where t2.property_id = t1.property_id and t2.requesting_user_id = t1.requesting_user_id
)
This works like a charm, but now I want to transform this (also as suggested in the SO answer I linked above) to a query using a join. So I came up with this:
select
created, property_id, requesting_user_id, type, response
from
pdr t1
inner join (
select max(created) as created, property_id, requesting_user_id
from pdr
group by property_id, requesting_user_id
) as t2 on t2.property_id = t1.property_id and t2.requesting_user_id = t1.requesting_user_id and t2.created = t1.created
Unfortunately this returns an error saying ambiguous column name: created. So I messed around with putting t1. or t2. before some of the created things, but then I get all sorts of syntax errors, so I'm kinda lost here.
Could anybody help me out in what I'm doing wrong here? All tips are welcome!
ps: I'm currently testing this on SQLite, but in the end it should work on MySQL as well. If there is a difference that would of course be very interesting to know also.
