Solved

Problem with MAX on datetime column in Mysql.

Posted on 2007-04-11
2
1,075 Views
Last Modified: 2008-02-01
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?

Thanks.
0
Comment
Question by:xfvgdrthbdtyvhgscv
2 Comments
 
LVL 11

Accepted Solution

by:
f_o_o_k_y earned 500 total points
Comment Utility
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
0
 
LVL 1

Author Comment

by:xfvgdrthbdtyvhgscv
Comment Utility
Points awarded by default to the only one who bothered to try to answer.  Thanks.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now