Help making a query more selective
Malk
SELECT DISTINCT alias, members.memberid, score, scores.date FROM
scores, members WHERE members.memberid=scores.memberid AND gameid=
$requestedgame ORDER BY score $scoresad LIMIT 0, 10
(I know this probably isn't the most optimised query) it selects
member and high score details from a table of high scores. What I'd
like it to do is select only the highest score for each member who has
saved a score for that particular game. I have tried using GROUP and
DISTINCT but I can't get it to work.
I know this is quite a simple thing to do, but my mySQL knowledge is
all self taught and is sadly still lacking quite a bit.
Thanks for the help,
Malcolm
Luuk
"Malk" <big_...@mac.com> schreef in bericht
news:1188037716.1...@q4g2000prc.googlegroups.com...
please share with us what u did try with that GROUP BY...
Malk
scores, members WHERE members.memberid=scores.memberid AND gameid=
This gives me one score from each member, which are ordered correctly,
but it is not the members highest score which is selected. It may be
the members lowest, or it might just be the most recent sorry I'm not
sure.
Luuk
so, you need to read this:
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html
and:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
suc6
Malk
SELECT alias, members.memberid, MAX(score), scores.date FROM scores,
members WHERE members.memberid=scores.memberid AND gameid=
$requestedgame GROUP BY(memberid) ORDER BY score DESC LIMIT 0, 10
Which is returning the data I want, thanks for the help Luuk. However,
it does seems to have broken the "ORDER BY score DESC" as the results
aren't in order any more. I can't see any reference to how the MAX()
function would change how the ORDER BY function works?
Luuk
try:
SELECT alias, members.memberid, MAX(score), scores.date FROM scores,
members WHERE members.memberid=scores.memberid AND gameid=
$requestedgame GROUP BY(memberid) ORDER BY MAX(score) DESC LIMIT 0, 10
or using an alias:
SELECT alias, members.memberid, MAX(score) as maxscore, scores.date FROM
scores,
members WHERE members.memberid=scores.memberid AND gameid=
$requestedgame GROUP BY(memberid) ORDER BY maxscore DESC LIMIT 0, 10
Malk
> "Malk" <big_...@mac.com> schreef in berichtnews:1188051541....@e9g2000prf.googlegroups.com...
Your first suggestion didn't work, "Invalid use of group function".
Your second suggestion worked perfectly :) I had no idea you could use
aliases in SQL queries!
Thanks a lot for your help, the site is www.standygames.com btw if
your interested :)
Malk
Luuk
This web site is parked free, courtesy of GoDaddy.com®
...
Malk
strawberry
> Sorry meant to typewww.standbygames.com, little typo
A better (more efficient) query could look something like this
(untested)...
SELECT s1.score, s1.date, s1.member_id, m.alias
FROM scores s1
LEFT JOIN scores s2
ON s1.member_id = s2.member_id
AND s1.score < s2.score
JOIN members m ON m.member_id = s1.member_id
WHERE s2.score IS NULL
AND s1.game_id = $requestedgame
ORDER BY score $scoresad;
Luuk
"strawberry" <zac....@gmail.com> schreef in bericht
news:1188113507.5...@r29g2000hsg.googlegroups.com...
because its 'untested', and look (only LOOKS) far more complex than the
other query,
i say (byt who am i ?) that this is not 'more efficient'....
when doing a short test, on different tables but aprox.same situation, i had
to change this query to SELECT DISTINCT, otherwise more of the same rows
where returned...
strawberry
> "strawberry" <zac.ca...@gmail.com> schreef in berichtnews:1188113507.5...@r29g2000hsg.googlegroups.com...
"More of the same rows" ? I don't know what that means.
In the event of a player scoring the same (highest) score on two
separate occasions you would need to add criteria to decide which
result to select. This could be based upon the highest game_id or the
most recent date. Using DISTINCT would return an arbitrary result
which may or may not be what you want. In any event, the query as
provided by LUUK would take longer. That's what's meant by
'efficient'. A good rule is "Never use a subquery where a JOIN would
do." Another good one is "Make all JOINS explicit". In other words,
don't use the "SELECT * FROM x,y" syntax but use "SELECT * FROM x JOIN
y ON x.key = y.key". It makes it much easier for yourself and others
to read and modify.
But what do I know.
So the revised query might look like this (tested):
SELECT s1.score, s1.score_date, s1.member_id, m.alias
FROM scores s1
LEFT JOIN scores s2 ON s1.member_id = s2.member_id
AND s1.score < s2.score
OR s1.member_id = s2.member_id
AND s1.score = s2.score
AND s1.score_date > s2.score_date
Luuk
>
>> when doing a short test, on different tables but aprox.same situation, i
>> had
>> to change this query to SELECT DISTINCT, otherwise more of the same rows
>> where returned...
>
> "More of the same rows" ? I don't know what that means.
doing a test on "on different tables but aprox.same situation" must have
gotten me into this situation that i needed to solve with DISTINCT,
and probably also got me in the situation that your solution was not quicker
when compared to my solution.....
i was not giving a solution to Malcom, because he stated he was
'self-learned', i only have him 2 links to mysql.com....
it solved his problem,
and i do think he has a hard time understanding your solution ;-)
strawberry
> "strawberry" <zac.ca...@gmail.com> schreef in berichtnews:1188157754.2...@y42g2000hsy.googlegroups.com...
I said it was "more efficient" - not necessarily "easier to
understand"! ;-)
For the benefit of the OP, and by way of explanation, let me
paraphrase from the manual:
The LEFT JOIN works on the basis that when s1.score is at its maximum
value, there is no s2.score with a greater value and the s2 row's
values will be NULL. See Section 12.2.7.1, "JOIN Syntax".