Anders Norrbring skrev:
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
Hi..
I just want to take this one more round with you all..
Would the above be the easiest and most "slim" and efficient way to
build the query?
I'd rather avoid using views and temporary tables.
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]
