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 <lists@stripped> 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/mysql?unsub=1
>>
>>
