MySQL Forums
Forum List  »  Performance

Assigning a varaiable without returning results
Posted by: Sean Nolan
Date: April 01, 2005 10:32AM

I want to check that I'm doing this the best way. I have a batch of SQL code, where I need to calculate an aggregate in SELECT and then use that value in a second SELECT. I am using MySql 4.0 so unfortunately I cannot use a subquery (which is how I'd like to do this :-)).

So I run the first SELECT and assign the aggregate to a variable something like this:
SELECT @AveragePrice := AVG(price) FROM Products;

Then I run the second SELECT, something like this:
SELECT * FROM Products WHERE price > @AveragePrice;

That works just fine, but I now get two result sets returned to the client, and I have to ignore the first one and use the second one.

I have found a workaround, in that I now use a CREATE TEMPORARY TABLE ... SELECT to run the first SELECT, it assigns the variable correctly, and then I run the second SELECT and I just get one result set on the client. It just seems like a heavy-handed way to do it. Is there a better way to assign the aggregate value to the variable without returning a result set from the first SELECT?


Options: ReplyQuote

Written By
Assigning a varaiable without returning results
April 01, 2005 10:32AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.