Avatar of phake123
phake123 asked on

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

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.

Any help is greatly appreciated
MySQL Server

Avatar of undefined
Last Comment
phake123

8/22/2022 - Mon
ASKER
phake123

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
mquiroz

unless you use mysql 4.1, mysql doesn't support subqueries...
Squeebee

An alternative is this:

Select @max := max(price) from rooms;

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

Execute the two in series within the same database connection.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Dossy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Squeebee

Dossy: We have already established that the user cannot use subqueries, so the first example os out.  As for the second one: did you try this? You are combining an aggregate function with non-aggregate functions, which will cause MySQL to error out on illegal syntax.
Dossy

Squeebee:  You're right, phake123 did say MySQL 4.0 -- oops.  And yes, the second query doesn't do what I thought.

Creating a simple test:

mysql> create table rooms (price int, name varchar(100));
mysql> insert into rooms values (10, "ten");
mysql> insert into rooms values (11, "eleven");        
mysql> insert into rooms values (15, "fifteen");        
mysql> insert into rooms values (5, "five");

This isn't optimal (as the ORDER BY still causes MySQL to do a full table scan and a filesort) but it works:

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

Returns the "15, fifteen" row.

However, your suggestion of using a variable to store MAX(price) and using that in a subsequent WHERE clause is definitely the most optimal solution since it CAN take advantage of indexes, etc.

Squeebee

Hi Dossy;

Sorry to nit-pick but there is one scenario you need to consider: What happens when more than one product exists at the highest price? In such a situation you will only return one of the highest priced items, but if you had two items that each cost $15 only one would be returned. I'm not criticizing, just giving your something else to think about.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dossy

Yup, you're right.
ASKER
phake123

Thanks everyone.  All your inputs were great!