MySQL - Specialized Order By?

Question

Hello.

Is there a way to specify a sorting procedure for ORDER BY, or some kind of custom logic? What I need is to check some other data for the column being ordered, which is also in the row. For example if one column has a higher value than another, but a certain condition isn't met, it's sorted as lower. Right now I pull all the data in the column, sort it in PHP with usort(), and then paginate it, but this is a pretty bad performance hog. I would really like to move it into MySQL, is it possible? If so, how? :P

Thanks in advance!

Example of problem on the website here - the records get sorted on win percentage, but players who have 1 game played turn out on top with 100 % win. I'd like to set a threshold on games and then sort them lower, even though their win percentage is higher.

Answer 1

You can order by multiple expressions:

ORDER BY games_played < 10, wins / losses DESC

The first expression sorts all those players who have played 10 or more games above all the players that have playes fewer than 10 games. The second expression sorts by win/loss ratio. The second expression is only used to tie-break rows that were equal for the first expression. This means that a player who has played 10 games will always appear above a player who has played only 9 games regardless of their win/loss ratios.