I've ran into a wall with the MAX() function when used with DATE_ADD(). I'm posting to the list in hopes that someone has an idea, comments, or suggestions for me. This is the problem at hand: Given: SELECT (TO_DAYS(MAX(DATE_ADD(T.StartDate, INTERVAL T.Duration Day)))) - (TO_DAYS(T.StartDate)) AS aDate FROM Tasks T; Explanation: StartDate = The start date of a task Duration = The length (in days) of a task I'm attempting to find the number of days between a Task's StartDate and the latest (MAX()) End Date in the table. I am using DATE_ADD with Duration instead of an EndDate column because the Duration field will make linking tasks easier for me in the future. When the above SQL code is executed on the table Tasks, the result I receive is correct and all is well in the world. The problem comes when a no GROUP column is added into the SELECT statement. For example: SELECT T.TaskName, (TO_DAYS(MAX(DATE_ADD(T.StartDate, INTERVAL T.Duration Day)))) - (TO_DAYS(T.StartDate)) AS aDate FROM Tasks T; This will not work because you can't mix No GROUP columns with GROUP columns WITHOUT a "GROUP BY" clause. Simple fix... add "GROUP BY TaskID". TaskID is the primary key and therefore unique so we shouldn't have problems grouping by it. Now I have: SELECT T.TaskName, (TO_DAYS(MAX(DATE_ADD(T.StartDate, INTERVAL T.Duration Day)))) - (TO_DAYS(T.StartDate)) AS aDate FROM Tasks T GROUP BY T.TaskID; I execute the above code which *should* be good and WOAH! Without changing my code for "aDate", the result has changed and exactly mirrors the Duration field. Take the no GROUP column out (T.TaskName in this case) and the GROUP BY clause and all is well in the world again. IF the no GROUP column is taken out but the GROUP BY clause is left in, I still get messed up results. I'm sure the GROUP BY has to be messing something up here, I just don't know what. I can't get rid of the GROUP BY if I'm using MAX() with other columns though. Any ideas, suggestions, creative criticism, etc, would be greatly appreciated. Other Notes: I've also tried "GROUP BY TaskName,ParentID" which is unique. Same results that mirror the Duration. Possibly GROUP BY is throwing off the DATE_ADD() or MAX() function? Thanks in advance for any help. -- Phil Stewart [EMAIL PROTECTED] - email (323) 402-1223 x7845 - voicemail/fax __________________________________________________ FREE voicemail, email, and fax...all in one place. Sign Up Now! http://www.onebox.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
