Sign in to follow this  
mike74

sql nightmare

Recommended Posts

mike74    100
Could someone help me get the following SQL statement 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
VerMan    116
As far as I know, SQL won't let you do that... you should do this:


CASE (100-COUNT(purchasedsquares.gameid))
WHEN 0 THEN 1
ELSE 0
END


Share this post


Link to post
Share on other sites
mike74    100
Verman, it says "invalid use of group function" when I tried your idea.

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

Share this post


Link to post
Share on other sites
mike74    100
Anyway, this seems to work:

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

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

Share this post


Link to post
Share on other sites
mike74    100
I thought you were suggesting that the case statement go after the "order by". Is that what you meant? For some reason, it doesn't work there.

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

Share this post


Link to post
Share on other sites
Michalson    1657
Quote:
Original post by VerMan
Isn't that the same?


No, not to MySQL. MySQL doesn't support most complex operations in the ORDER BY section. He solved it by basically "precomputing" the result as a column, then sorting by that column (effectively tricking MySQL with a round about approach). Obviously you take a small performance hit to return the extra column to the user, but it's the only way to work around the limitations.

Quote:
Original post by mike74
I thought you were suggesting that the case statement go after the "order by". Is that what you meant? For some reason, it doesn't work there.

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


Again, it will work in both places if you are using a standard SQL provider. MySQL is not standard, it's mostly composed of kludges glued onto a select engine. Because of this it has many gotcha's where the feature emulation was only created for the most obvious usage.

Share this post


Link to post
Share on other sites
VerMan    116
oh, I missed the ORDER BY part...
I focused on the CASE, where you had to use the actual operation instead of the precomputed value...

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