Solved

Problem with MAX on datetime column in Mysql.

Posted on 2007-04-11
2
1,076 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
ID: 18891060
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
ID: 18905814
Points awarded by default to the only one who bothered to try to answer.  Thanks.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

947 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

21 Experts available now in Live!

Get 1:1 Help Now