Problem with MAX on datetime column in Mysql.
Posted on 2007-04-11
Using Mysql, and having a problem with the MAX function on a datetime column.
Table accounts has information about some accounts, with many columns. Three of the columns are: account_id, type=integer,
contact_id, type = integer,
and last_update, of type datetime.
Every time any account's info gets updated, this table gets a new row to record the date and time of the update. The contact for an account can change.
So I want to know what are all the accounts for which contact_id X is still the contact. That is, all the accounts that have contact_id=X in their most recent update records.
I am stuck with Mysql 3.0, so I do not have access to sub-queries. So I did the following:
SELECT account_id, MAX(last_update) FROM accounts WHERE (contact_id = 'X') GROUP BY account_id;
expecting to get the account_id of all accounts that have ever had X as their contact-Id, plus the date of tthe most recent update for each account. Then I would get that most recent record for each and compare contact_ids to see if this contact is still the contact for that account.
BUT - my SELECT is returning not the lastest update datetime, but the latest update of the first day. That is, if account Y has three entries with last_update values of:
it is pulling out the 2007-03-20 10:30:00 value - the last datetime in whatever is the first day of updates.
What am I doing wrong?