Solved

Problem with MAX on datetime column in Mysql.

Posted on 2007-04-11
2
1,079 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL  on Tomcat 8 94
CLI command keep running after close 7 56
Where on a calculated field 1 30
MySQL Persistent Connections 10 32
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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