Retrieving the last record
I have been trying to execute a query that will not return the correct results, but only for 1 property(we are in real estate). I am trying to return the last record that was entered in the database for a particular client_id and prop_id. For some reason all of the properties return the correct ca_id except 1. And if I try the MAX() function then it will return the correct ca_id for that 1 property but all of the other information associated with that record is not returned correctly. For example, it will return ca_id = 210 but all of the information related to ca_id = 209 will show up instead. Can someone explain to me what I am doing wrong? Any input is appreciated greatly! Here is my query:
SELECT t1.ca_id, t2.ca_id, t1.prop_id, t1.portfolio_id, t1.accepted, t1.expired, t1.deactivate, p.name, p.city, ps.state_abbr, p.address, p.date_opened, p.units, p.structure, p.thumb_image_name, p.confidential, p.listing_desc, p.status_id, p.encode_email, p.staff_id, p.last_updated, port.portfolio_name, port.portfolio_thumb_image, port.encode_email AS port_encode_email, port.status_id AS port_status_id, port.staff_id AS port_staff_id
FROM ca_stats t1
LEFT JOIN ca_stats t2 ON t1.prop_id = t2.prop_id AND t1.client_id = t2.client_id AND t1.ca_id < t2.ca_id
LEFT JOIN weblink_stats w ON t1.prop_id = w.prop_id
LEFT JOIN properties p ON t1.prop_id = p.prop_id
LEFT JOIN properties_state ps ON p.state_id = ps.state_id
LEFT JOIN portfolio port ON t1.portfolio_id = port.portfolio_id
WHERE t1.client_id = 162 AND t1.no_show = 0
GROUP BY t1.prop_id
ORDER BY t1.expired ASC, t1.accepted DESC, p.status_id ASC, quality DESC