Alright, I am almost there, got one problem now. There are multiple entries for each id in table2. I need only the records with the newest timestamp for each id. Want to do something like this, but doesn't work:
INSERT INTO table_tmp (id,count_new,count_old,date) SELECT table1.id, table1.count, table2.count, CURRENT_DATE() - 1 AS date FROM table1,table2 WHERE table2.id=table1.id AND table1.count!='Nope' OR 'NULL' AND timestamp=MAX(table2.timestamp) GROUP BY table2.id; This gives me: ERROR 1111: Invalid use of group function So I tried something like: INSERT INTO table_tmp (id,count_new,count_old,date) SELECT table1.id, table1.count, table2.count, CURRENT_DATE() - 1 AS date FROM table1,table2 WHERE table2.id=table1.id AND table1.count!='Nope' OR 'NULL' GROUP BY table2.id HAVING MAX(timestamp); Which will return 0 results every time. Can't use 'ORDER BY desc limit 1' since that will only give me 1 entry. Anything else I can try? There has to be a way to get all the counts from table2 which have the newest timestamp for each id in the table! Please help! Roy -----Original Message----- From: Michael Iatauro [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 4:27 PM To: [EMAIL PROTECTED] Subject: Re: Complex select statement As far as I know, MAX only returns the greatest row, so what you really want at the end is WHERE table_tmp.id=table2.id AND table2.timestamp=MAX(table2.timestamp) ~MJI Roy Walker wrote: > Still having a problem with this. Still have one last thing that isn't working. This is MySQL 4.0.13. > > UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp); > > This is giving me: > ERROR 1111: Invalid use of group function. > > I am trying to update the period_count field for all the records in table_tmp, by setting the equal to the count from table_tmp MINUS the count from table2 where the id's match and it is the newest record in table2. > > Any ideas? > Please don't tell me to how to do it 4.1! Please! :) > > Roy > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
