Albert Padley wrote:

> I have the following query that has worked fine for displaying
> standings for a soccer league.
>
> SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER
> BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC
>
> As I said, works fine. Now, however, the league wants a slightly
> different sort order. They only want to sort on the spts column if
> the difference between 2 teams is greater than 9 in the spts column.
> All other sort criteria remain the same. So, the ORDER BY would be
> tpts DESC, spts DESC (but only if the difference is > 9), w DESC, ga
> ASC, team_number ASC.


if spts is an integer so that > 9 is the same as saying >= 10 then you
could sort by a rounded version of spts like this...

ORDER BY tpts DESC, ROUND(spts,-1) DESC, w DESC, ga ASC, team_number ASC

by putting the -1 there it rounds to the nearest 10's before doing the
sort. Of course the output is not rounded. The following are a few
examples of the output of the round statement.

ROUND(23.632, 2) = 23.63
ROUND(23.632, 1) = 23.6
ROUND(23.632, 0) = 24
ROUND(23.632, -1) = 20


--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com