Sign in to follow this  

simple SQL question

This topic is 4548 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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

This topic is 4548 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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