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]
