sql nightmare

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

Recommended Posts

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 on other sites
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 on other sites
Verman, it says "invalid use of group function" when I tried your idea.

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

Share on other sites
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 on other sites
Isn't that the same?

Share on other sites
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 on other sites
Quote:
 Original post by VerManIsn'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 mike74I 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 on other sites
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...

1. 1
2. 2
Rutin
24
3. 3
4. 4
JoeJ
19
5. 5

• 14
• 26
• 11
• 11
• 9
• Forum Statistics

• Total Topics
631770
• Total Posts
3002251
×