Unanswered: Select Last Updated Row For Each User Id
Hi Fellow MySQLers,
As the title hints at, I am trying to create a query that will select that lastupdated row for each userid. I have two tables that I want to join in the query, one called "sales" and another called "contacts" using the variable "contactid". I would like the results from the query to populate a page for each "sales id" with the last updated information. However, I am having some trouble creating the correct query to get the results I need.
Currently, I have put together the following query that works:
$query = "SELECT contacts.company,sales.leadid,sales.service,sales. product,sales.inquirydate,sales.followup,sales.las tupdatedon FROM contacts,sales WHERE contacts.contactid = sales.contactid AND (status = 'Initial Contact' OR status = 'Initial Response') ORDER BY leadid DESC LIMIT $page, $limit";
The result of this gives me a description of every sales lead. However, I want to be able to show the information in most its up-to-date state only for each sales lead.
Logic tells me that I want to SELECT my info FROM my tables WHERE MAX(lastupdatedon) FOR EACH LEADID. Of course, my language is not compliant with mysql language. Can anyone offer some assistance? I am sure it is some simple little word I have to add to get it to work. THANKS IN ADVANCE!!!!
Thanks for your help! This is yet another reason for me to move my site now, which I plan on doing in the next month because there is no point for it to be hosted 4000 miles away.
Anyhoo, I tried the code and it worked...almost.... It returned the necessary info for all but four "leads" each with a unique leadid. I am assuming that this is because each of the missing leads is from the same company and hence shares the same contact id. I have poked around with the code to try and figure out why the four leads are being omitted, but cannot seem to isolate the problem. I guess it has something to do with the GROUP BY or HAVING parts, but can't seem to determine the problem. Any more hints please???
I know what you mean, but I also like to have the ability to go talk face-to-face if need be. Currently mine is in Van, but I moved to Hong Kong. Other problem with this is my days is their nights and usually they are fast alseep when I have an issue.
So I played around with it more and found this, if I insert s2.leadid in the GROUP BY section, then the four leads that share the same contact id as the other that is showing reappear. The code looks like...
$query = "SELECT contacts.company, s1.leadid, s1.service, s1.product, s1.inquirydate, s1.followup, s1.lastupdatedon FROM contacts INNER JOIN sales AS s1 ON contacts.contactid = s1.contactid AND s1.status IN ('Initial Contact','Initial Response') INNER JOIN sales AS s2 ON contacts.contactid = s2.contactid AND s2.status IN ('Initial Contact','Initial Response') GROUP BY contacts.company, s1.leadid, s1.service, s1.product, s1.inquirydate, s1.followup, s1.lastupdatedon, s2.leadid HAVING s1.lastupdatedon = max(s2.lastupdatedon) ORDER BY s1.leadid DESC LIMIT $page,$limit";