how to get next date?

[mwm]

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?

Thanks

[Fumigator]

SELECT MAX(contactDate) FROM contactTable where buyer = $buyer will select the highest (most current) date. Is that what you're after?

[mwm]

Yes that's what I was going for, but still can't make it work. Here is the query

PHP Code:

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


[Fumigator]

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.

SELECT *
FROM table1 as a
WHERE date1 = (select MAX(date1) from table1 as b WHERE a.id = b.id)