Sign in to follow this  
mike74

simple SQL question

Recommended Posts

Okay. I am making a football squares game, and the guy I'm working for wants the different games available to be sorted by the squares remaining. However, he wants the ones with 0 squares remaining to appear at the end. Does anyone know a single SQL statement that can achieve this? It's pretty close to SELECT * from standardgame order by squaresremaining asc; However, there are those games with 0 remaining that need to appear at the end. Here's his example: Game Squares Name Remaining Team 1 Team 2 etc................ _______________________________________________________ Test1 7 Oakland Denver Junks 26 Pitt Oak Test2 75 A B Super 0 Raiders Broncos Mike C. http://www.coolgroups.com/zoomer/

Share this post


Link to post
Share on other sites
Ok. It appears I may have tried to simplify the problem too much in my original posting. Here's the real SQL statement I'm trying to get working:

SELECT
standardgame.gamename,
standardgame.rowteamname,
standardgame.colteamname,
standardgame.dollarvalue,
standardgame.gameid,
standardgame.cutoffdatetime,
standardgame.gametype,
standardgame.gameowner,
(100-COUNT(purchasedsquares.gameid)) AS squaresremaining
FROM standardgame
LEFT OUTER JOIN
purchasedsquares ON standardgame.gameid = purchasedsquares.gameid
where gametype='$gametype' and dollarvalue = '$dollarvalue' and gameowner = ''
GROUP BY standardgame.gameid
order by
CASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,
squaresremaining ASC


The problem is... MySQL doesn't seem to want to let me use squaresremaining in that case statement since it's not an official column name. Any idea how I can reference squaresremaining in the case statement?

Mike C.
http://www.coolgroups.com/zoomer/

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this