I have a contact form that inserts the date the last time a sales person has made contact with the buyer. I want to display this in a report, so when they run the report it show the last date contact was made. something like select next closes date to current date. any ideas how this could be done?
03-20-2007, 08:52 PM
SELECT MAX(contactDate) FROM contactTable where buyer = $buyer will select the highest (most current) date. Is that what you're after?
Yes that's what I was going for, but still can't make it work. Here is the query
$mysql = ("select cust.id,cust.fname,cust.lname,cust.address,cust.city,cust.state,cust.zip,cust.hphone,cust.community, date_format(cust.regdate, '%m-%d-%Y'), sum(followup.tpcount) as t_count, MAX(followup.tpdate) from cust, followup where cust.regdate < curdate() - interval 0 day and community = '$community' and cust.id = followup.id and status = 'Prospect' group by lname having t_count < 1");
03-20-2007, 10:54 PM
SUM() and MAX() are column functions so they can't be mixed with non-column functions... basically when you do a "GROUP BY" you you can only select columns that are in that GROUP BY clause.
A way around this is to do a subquery, but your version of MySQL must be 4.1 or greater.
FROM table1 as a
WHERE date1 = (select MAX(date1) from table1 as b WHERE a.id = b.id)