Jump to content


Photo

mysql ORDER BY


  • Please log in to reply
2 replies to this topic

#1 birdie

birdie
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts
  • LocationBirmingham UK

Posted 05 April 2006 - 08:43 PM

hi, ive used the order by function loads of times but have never used it this way.

i have a mysql table like this..

id|clanname|win|draw | loose |
====================
1|name1 |3 |2 | 1 |
2|name2 |4 |3 | 2 |


how would i output the clannames ordered by the highest score first?

win = 2 points
draw = 1 point
loss = 0 points

would it be something like this..

"SELECT clanname FROM table ORDER BY win*2,draw";

i really have no clue and am now guessing at this time

any help appreciated, thanks

#2 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 05 April 2006 - 10:34 PM

i would create an intermediate value in your SELECT query, and refer to that in your WHERE clause:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] stuff, (wins*2 + draws - loose) AS points FROM table WHERE stuff ORDER BY points DESC [!--sql2--][/div][!--sql3--]

alternatively you can operate a much simpler ORDER BY, but i don't think it will give you what you want:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] stuff FROM table WHERE stuff ORDER BY wins DESC, loose ASC, draws DESC [!--sql2--][/div][!--sql3--]

that will select those with the most wins first. any ties in that set will then be grabbed in order of increasing losses. any ties in THAT will then be grabbed in order of most draws to fewest.

#3 birdie

birdie
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts
  • LocationBirmingham UK

Posted 06 April 2006 - 09:46 PM

ok thanks :-D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users