Problem with MAX on datetime column in Mysql.

Hello all,

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:

2007-03-20 10:00:00
2007-03-20 10:30:00
2007-03-25 08:00:00

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?

Who is Participating?
f_o_o_k_yConnect With a Mentor Commented:
Wow mysql 3.0 try to search if there wasnt any bug with max function.

but as an workaround try using conversion (for example to string or timestamp) inside MAX function
xfvgdrthbdtyvhgscvAuthor Commented:
Points awarded by default to the only one who bothered to try to answer.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.