List:General Discussion« Previous MessageNext Message »
From:Anders Norrbring Date:December 20 2007 10:08pm
Subject:Re: Another cry for help..
View as plain text  
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
>>
>>


Thread
Another cry for help..Anders Norrbring20 Dec
Re: Another cry for help..Anders Norrbring20 Dec
Re: Another cry for help..Anders Norrbring20 Dec
  • Re: Another cry for help..Anders Norrbring20 Dec
    • Re: Another cry for help..Anders Norrbring23 Dec
  • Re: Another cry for help..Jay Pipes20 Dec
Re: Another cry for help..Anders Norrbring20 Dec