Jump to content
  • Advertisement
Sign in to follow this  
mike74

simple SQL question

This topic is 4794 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
Advertisement
SELECT * from standardgame
ORDER BY
CASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,
squaresremaining ASC;

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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!