From: Anders Norrbring (listsnorrbring.se)
Date: Thu Dec 20 2007 - 16:18:12 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <listsnorrbring.se> 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=brenttechgmail.com
>>>
>>>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]