You could use a view:
CREATE VIEW all_scores
SELECT s.tid, s.vid, s.uid, s.highScore
FROM score s
JOIN objects o
ON s.tid = o.tid
JOIN itemtypes it
ON s.vid = it.vid
JOIN users u
ON s.uid = u.uid
WHERE o.shortname = %s /* Should these ANDs really be ORs? */
AND i.itemtype LIKE %s;
SELECT highScore:= @my_high_score
FROM all_scores
WHERE u.username = %s
LIMIT 1;
SELECT COUNT(*):= @total_scores FROM all_scores;
SELECT COUNT(*):= @total_greater_my_score
FROM all_scores
WHERE highScore > @my_high_score;
SELECT ((@total_great_my_score + 1) / @total_scores) * 100 AS percentile;
Hope this helps,
Jay
Anders Norrbring wrote:
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]
