Brent,
you put me on the right track.. ;) Reading up a bit on syntax for
variables, I came up with the following, which seems to work.
SET @tid:=(SELECT tid FROM objects WHERE shortname = %s);
SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s);
SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = @tid AND s2.vid = @vid) * 100 AS percentile
WHERE s1.tid = @tid AND s1.vid = @vid
AND s1.highScore > (SELECT highScore FROM score WHERE tid = @tid AND
vid = @vid AND uid = (SELECT uid FROM users WHERE username = %s))
Setting the vars with SET before doing the SELECT seems to do it..
Anders
Anders Norrbring skrev:
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]
