Brent Baisley skrev:
You're right that wouldn't work because you can't assign the query
results to a variable. You want to put the variables in the query and
assign them to the value of the field.
...WHERE s2.tid = (SELECT @tid:=tid AS tid FROM objects WHERE shortname = %s)...
That should work better, or at least put you on a better track.
I haven't checked this yet, but would it be better/smoother than putting
the variables assignments in separate SET statements in front of the
SELECT as I mentioned?
Anders
On 12/20/07, Anders Norrbring <[EMAIL PROTECTED]> wrote:
Brent Baisley skrev:
You might be able to use variables to store the result of the query.
Although I've never tried assigning the result of a query to a
variable, only field values.
SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s)
AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s)
) * 100
AS percentile
WHERE s1.tid = @tid
AND s1.vid = @vid
AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
vid = s1.vid
AND uid = (SELECT uid FROM users WHERE username = %s))
Brent
At a first glance, it doesn't work at all, I get NULL results from it,
but I haven't spent any time trying to locate the problem yet..
So, I'm still open for ideas!
On 12/20/07, Anders Norrbring <[EMAIL PROTECTED]> wrote:
Hi.. I'm struggling with a query that I'm trying to simplify as much as
possible, but I can't seem to get rid of using the very same subqueries
several times.
Would there be a way to optimize the following so I get rid of
subqueries that do the exact same thing more than once?
SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
AS percentile FROM score AS s1
WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
vid = s1.vid
AND uid = (SELECT uid FROM users WHERE username = %s))
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
