Subquery doesn't work, syntax is correct???

phake123 · #1 (**permalink**) 04-03-04, 20:23

I just installed Mysql 4.0.18

I tried to run ths sql statement

Select a.price, a.name
from rooms
where a.price = (Select max(price) from rooms);

I don't believe the syntax is wrong. Can anyone help me? I checked the data in the [Rooms] table which are all valid.

When I run:
Select max(price) from rooms;

It works, but when its in a subquery it doesn't.

I get this error:
Error 1064: You have an error in your SQL syntax. Check the manual that correspond to your MYSQL server version for the right syntax to use near 'Select max(price) from rooms' at line 3

Any help is greatly appreciated

guelphdad · #2 (**permalink**) 04-03-04, 22:02

You can't use subqueries before version 4.1

r937 · #3 (**permalink**) 04-04-04, 09:41

workaround:

PHP Code:


			
select t1.price

     , t1.name

  from rooms as t1

cross

  join rooms as t2

group

    by t1.price

     , t1.name

having t1.price = max(t2.price)

aus · #4 (**permalink**) 04-04-04, 16:01

Or, since HAVING is not optimized in MySQL and a CROSS JOIN could be expensive on a large table, you could do:

Code:

SELECT price, name FROM rooms
ORDER BY price DESC LIMIT 1;

Internet Services

Web Development

Digital Marketing

Consumer Tech