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!!!!

it's easy if you can use a subquery, a little messier if you cannot

you didn't say which version you're on

I think my ISP is running 3.23.58. Thanks for your help and say hello to the TO for me.

Tell your ISP they are seriously lagging with the version they are running. As Rudy mentioned in the sticky thread at the top of this list 4.1 has been the full production version since Oct. 2004.

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???

Thanks!!!

it doesn't really matter how far away you are from y9our host, what counts is how far away your site visitors are

for example, i'm in toronto canada, and my site is in phoenix arizona

your four missing leads might have been dropped either because of the WHERE condition, or because (less likely) they didn't have a valid lastupdatedon value

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";

Any ideas on why this works?

THANKS!!!!!!!!!!!!

if your host doesn't have 24/7 support, get a new host

adding a column to the GROUP BY like you did is not a good idea, unless you really know how grouping works and why there's a self-join in this query