Re: Sort Problem SOLVED

[Albert Padley]

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]