SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 09:17 PM
| ||||
| ||||
 Sort Problem 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 it is possible to form such a query, I need help in what is would look like. Thanks. Al Padley      |
|
02-27-2008, 09:17 PM
| |||
| |||
| Re: Sort Problem On Sep 15, 2006, at 12:56 PM, Chris W wrote: > 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 Yes, that seems to work well. Thanks. Al Padley |
|
02-27-2008, 09:17 PM
| ||||
| ||||
| Re: Sort Problem 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 |
Linear Mode
