Syntax problem

[Nik Coughin]

I am getting:

#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'SELECT max( yearmonth )
FROM periods )
ORDER BY mr.currentra

With this query:

select b.boatname, mr.currenttcf, mr.startingtcf, mr.averagetcffo rmonth,
mr.totalraces, mr.racesinmonth ,
mr.currentranki ng, mr.startofmonth ranking, mr.rankingchang einmonth,
design
from MonthlyRankings mr, boat b
where mr.boatid = b.boatid
and mr.yearmonth = (select max (yearmonth) from periods)
order by mr.currentranki ng

It works fine if I remove:
and mr.yearmonth = (select max (yearmonth) from periods)

It also works if I change it to:
and mr.yearmonth = '2004-11-01'

And this works by itself, and returns '2004-11-01':
select max (yearmonth) from periods

So, it makes no sense to me that the syntax is invalid, any ideas?

[Bill Karwin]

Nik Coughin wrote:

and mr.yearmonth = (select max (yearmonth) from periods)

My first guess is that you're using MySQL 4.0 or earlier.
You must use MySQL 4.1 to use subqueries.

Regards,
Bill K.

[Nik Coughin]

Bill Karwin wrote:

Nik Coughin wrote:

and mr.yearmonth = (select max (yearmonth) from periods)

My first guess is that you're using MySQL 4.0 or earlier.
You must use MySQL 4.1 to use subqueries.

Regards,
Bill K.

I believe you may be right, I'll just run the subquery seperately then.
Thanks Bill.