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] >
