MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Roy Walker Date:July 16 2003 12:59am
Subject:RE: Complex select statement
View as plain text  
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:miatauro@stripped] 
Sent: Tuesday, July 15, 2003 4:27 PM
To: mysql@stripped
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/mysql?unsub=1

Thread
Complex select statementRoy Walker11 Jul
RE: Complex select statementRoy Walker11 Jul
  • Re: Complex select statementEgor Egorov11 Jul
  • Re: Complex select statementNils Valentin12 Jul
RE: Complex select statementRoy Walker11 Jul
RE: Complex select statementRudy Metzger14 Jul
RE: Complex select statementRoy Walker15 Jul
  • Re: Complex select statementMichael Iatauro15 Jul
RE: Complex select statementRoy Walker16 Jul
RE: Complex select statementRudy Metzger16 Jul