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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
try this. It won't be as fast but it will sort correctly: SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, if ((ref_pts+adjust_ref_pts)15 ,game_pts + 15, game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Shawn Green Database Administrator Unimin Corporation - Spruce Pine Albert Padley [EMAIL PROTECTED] wrote on 10/21/2004 11:48:09 PM: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
This will solve your problem and remove the need for the PHP correction. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN game_pts + 15 ELSE game_pts + ref_pts + adjust_ref_pts END AS total_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN 15 ELSE ref_pts + adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem SOLVED
Jeff and Shawn, Thanks for coming up with similar solutions. Jeff, I have used yours because it was more complete. I wasn't aware of the Case statement in mysql. I guess I still have a lot to learn. Thanks again. Albert On Oct 22, 2004, at 10:10 AM, Jeff Burgoon wrote: This will solve your problem and remove the need for the PHP correction. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN game_pts + 15 ELSE game_pts + ref_pts + adjust_ref_pts END AS total_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN 15 ELSE ref_pts + adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Juan, Good question. ref_pts are given for completing referee assignments and are tracked throughout the season and can exceed 15 for tracking purposes. However, only a max of 15 total_ref_pts are used in determining the standings. adust_ref_pts (up or down) are given by the Referee Assignor for situations not covered in the normal automatic system. Again, they want to track this total throughout the season, but only use a max of 15 total_ref_pts for computing the standings. In your example, they want to know the true values of 10, 9 and 7, but total_pts would only add up to 25 for computing the standings (10 game_pts and 15 total_ref_pts). Make sense? Albert On Oct 22, 2004, at 11:46 AM, Juan M. Quiroz wrote: I have some questions. Is the information on those fields used as their true value? Meaning are you always have to manipulate the data, then why not enter the correct information instead of manipulating. If this is a special case I can see is necessary. so if the values for game_pts, ref_pts, adjust_ref_pts are 10, 9, 7 can it take its true add of 26 or should it be 25? Then why not have the right values to begin with. Just curious. Juan Albert Padley on 10/21/04 at 9:48 PM -0600 wrote about: Sort Problem I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sort problem
Store town numbers in another column. [EMAIL PROTECTED] wrote: Hi, How can I fix an order by using numbers and letters ? Id Town 56 Paris 1 60 Paris 10 7 Paris 11 262 Paris 12 8 Paris 13 16 Paris 14 22 Paris 15 6 Paris 3 57 Paris 4 51 Paris 6 5 Paris 7 61 Paris 8 59 Paris 9 I'd like to get : 56 Paris 1 6 Paris 3 57 Paris 4 51 Paris 6 5 Paris 7 61 Paris 8 59 Paris 9 60 Paris 10 7 Paris 11 262 Paris 12 8 Paris 13 16 Paris 14 22 Paris 15 A this time I'm doing this sql syntax : SELECT * FROM town ORDER BY town ASC How can I do it ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sort problem
Nicolas, How can I fix an order by using numbers and letters ? Id Town 56 Paris 1 60 Paris 10 7 Paris 11 I'd like to get : 56 Paris 1 6 Paris 3 57 Paris 4 A this time I'm doing this sql syntax : SELECT * FROM town ORDER BY town ASC SELECT * FROM town ORDER by town ASC, district ASC Assuming that you have district in a different column. If not, you're database design is somewhat not optimal, and you should change it. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sort problem
If your town field always uses the format town number, you can do:
SELECT *
FROM town
ORDER BY substring(town,locate(' ',town))+0 ASC;
On Sat, 2003-02-08 at 11:40, Nicolas JOURDEN wrote:
Hi,
How can I fix an order by using numbers and letters ?
Id Town
56 Paris 1
60 Paris 10
7 Paris 11
262 Paris 12
8 Paris 13
16 Paris 14
22 Paris 15
6 Paris 3
57 Paris 4
51 Paris 6
5 Paris 7
61 Paris 8
59 Paris 9
I'd like to get :
56 Paris 1
6 Paris 3
57 Paris 4
51 Paris 6
5 Paris 7
61 Paris 8
59 Paris 9
60 Paris 10
7 Paris 11
262 Paris 12
8 Paris 13
16 Paris 14
22 Paris 15
A this time I'm doing this sql syntax :
SELECT *
FROM town
ORDER BY town ASC
How can I do it ?
--
Diana Soares
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SORT problem
Hi. I am trying to create a SQL statement that sorts by a column that contains a mix of numbers and periods, but it doesn't seem to work properly. The statement I use is: SELECT ItemNumber FROM Catalog ORDER BY ItemNumber For instance, here is how the list was sorted: 5.2.8 5.2 5.3 5.13 5.10 5.27 But it should be: 5.10 5.13 5.2 5.2.8 5.3 5.27 Are there any additional parameters you can specify or another method for sorting alphanumeric strings such as this? Thanks for your help, Camilo Rostoker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
