Sort

[Taylor_1978]

Howdy,

I have a rather long query... this is it:


$select = mysql_query("SELECT
R.uid,
R.rank,
U.alias,
U.membership,
R.premium,
R.rating,
sum((M.wid=R.uid AND M.season='$LINFO[season]')+(M.lid=R.uid AND M.season='$LINFO[season]')) as PMplayed,
sum(M.wid=R.uid AND M.season='$LINFO[season]') as PMwon,
sum(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER') as PMwonHIGHER,
sum(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='SAME') as PMwonSAME,
sum(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='LOWER') as PMwonLOWER,
sum(M.lid=R.uid AND M.season='$LINFO[season]') as PMlost,
sum((M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='LOWER')-(M.lid=R.uid AND M.season='$LINFO[season]')) as points,
R.penalty,
sum((M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.uid AND M.season='$LINFO[season]' AND M.diff='LOWER')-(M.lid=R.uid AND M.season='$LINFO[season]'))-(R.penalty) as TP
FROM ranks R LEFT JOIN matchdb M ON M.ladder=R.ladder LEFT JOIN users U ON U.id=R.uid
WHERE M.ladder='$_REQUEST[ladder]' AND division='$division'
GROUP BY R.uid
ORDER BY TP DESC") or die(mysql_error());
while($row = mysql_fetch_array($select)){
$rank++;
mysql_query("UPDATE ranks SET rank='$rank' WHERE uid='$row[uid]' AND ladder=$ladder AND division=$division");
} // while


Basically what this does is counts how many times a player has won a game against someone ranked in a higher divsion (awards 3 points), same division (awards 2 points) and lower division (awards 1 point) and how many losses (takes away 1 point) and places them in order of their total points.

My problem is, I want it to order by points but only those who have actually played first, and then anyone who hasnt played to be ordered after it. But at current, someone who has lost 1 game and has -1, is ranked lower than someone who hasnt played and has 0 points.

I'm not sure how to go about changing this query to suit what I'm after. Any ideas?

Thansk in advance! :thumbsup:

[Taylor_1978]

Nevermind... After trying to work this out for many hours.. it suddenly just came to me minutes after posting.. and couldnt have been more obvious.. just add AND PMplayed > 0 in the where statement, then do a second query as PMplayed = 0. Unless someone has a better way?

[Taylor_1978]

Sorry for triple post.. that didn't work.. back to drawing board.. is saying, unknown column PMplayed. :(

[guelphdad]

you don't want a WHERE clause for that you want to add a HAVING PMPlayed > 0 or something like that. you add the HAVING clause after the group by. your group by is incorrect by the way and can cause unpredictable results. Search the mysql manual on GROUP BY HIDDEN FIELDS.