MySQL Forums
Forum List  »  InnoDB

Retrieving the last record
Posted by: Josh Stockdale
Date: July 15, 2008 11:54AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Retrieving the last record
4097
July 15, 2008 11:54AM
2341
July 15, 2008 12:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.