View Full Version : how to get next date?

03-20-2007, 07:01 PM
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?

03-20-2007, 10:09 PM
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)