Solved

Problem with MAX on datetime column in Mysql.

Posted on 2007-04-11
2
1,078 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP loop not working 4 71
MySql hide the stored procedures 2 54
UPDATE query not working in mysqli php 8 60
SubQuery link 4 35
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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

808 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