Solved

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

Posted on 2004-04-03
9
358 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:phake123
  • 3
  • 3
  • 2
  • +1
9 Comments
 

Author Comment

by:phake123
ID: 10750209
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
0
 
LVL 7

Expert Comment

by:mquiroz
ID: 10750764
unless you use mysql 4.1, mysql doesn't support subqueries...
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 10755076
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.
0
 
LVL 1

Accepted Solution

by:
Dossy earned 50 total points
ID: 10756530
What you want is this:

SELECT   a.price, a.name
FROM     rooms AS a
WHERE   a.price IN (
    SELECT   MAX(price)
    FROM     rooms
);

However, from this particular query I have a feeling what you REALLY want is this:

SELECT   price, name
FROM      rooms
HAVING   price = MAX(price);

The first query using the subquery will do two scans of the table -- once for the outer query, and once for the inner subquery.  The second query (with the HAVING clause) should only scan the table once, which should be somewhat faster.

-- Dossy
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:Squeebee
ID: 10756818
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.
0
 
LVL 1

Expert Comment

by:Dossy
ID: 10757365
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.

0
 
LVL 17

Expert Comment

by:Squeebee
ID: 10757440
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.
0
 
LVL 1

Expert Comment

by:Dossy
ID: 10759036
Yup, you're right.
0
 

Author Comment

by:phake123
ID: 10760662
Thanks everyone.  All your inputs were great!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now