473,322 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

sql trouble

bob
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?

Jul 23 '05 #1
1 1301
bo*@coolgroups.com wrote:
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?


Hmm. Apparently you can't use column aliases in expressions in the
ORDER BY clause, even though you can use the column aliases in ORDER BY
without being in an expression.

You could add another computed column `squaresremaining_sortorder` that
contains the 1 or 0 you need to sort by? You can't reference the column
alias in an expression in the select-list either, but you can use a
duplicate expression. For example:

SELECT ...
(100-COUNT(purchasedsquares.gameid)) AS squaresremaining,
CASE (100-COUNT(purchasedsquares.gameid)) WHEN 0 THEN 1 ELSE 0 END
AS squaresremaining_sortorder
FROM standardgame
...
ORDER BY squaresremaining_sortorder ASC, squaresremaining ASC

Regards,
Bill K.
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: James | last post by:
Hi I am having some trouble getting a asp page to load. Im a noob to the asp side. I have followed knowledege base Article 301305. I am running 2000 adv, IIS 5.0 I have the following...
4
by: Jacek Dziedzic | last post by:
Hi! First of all, I hope my problem is not too loosely tied to the "standard C++" that is the topic of this group. I have some code that exhibits a strange behaviour: on one computer, where I...
0
by: Paul C | last post by:
Hello, everybody. I am having trouble running some of the VS.NET samples, specifically the CarSelector web app, which is very simple. The symptom is that the web controls (drop down listboxes and...
0
by: Alexandre Jaquet | last post by:
Hi does anybody know how to solve my trouble, when I try to create a MS Office project I always got trouble. I can't create office project when I try to create one vs.net restart ... :s
2
by: Jeff | last post by:
/* -------------------------------------------------------------------------- Hello, I was experimenting with class templates and specializing member functions and came across a simple problem...
1
by: Jim Bancroft | last post by:
Hi everyone, I'm running into a problem with my ASP.Net application. I've just created a new aspx page which uses some new components of mine that inherit from ServicedComponent and are...
6
by: Daniel Walzenbach | last post by:
Hi, I have a web application which sometimes throws an “out of memory” exception. To get an idea what happens I traced some values using performance monitor and got the following values (for...
3
by: Olivier BESSON | last post by:
Hello, I have a web service of my own on a server (vb.net). I must declare it with SoapRpcMethod to be used with JAVA. This is a simple exemple method of my vb source : ...
2
by: JLupear | last post by:
I am having trouble with my code again, I had prepared a question and the code to upload, however I am having trouble posting it, are there limits to the amount of lines you can post? I split it...
0
by: mrchatgroup | last post by:
news from http://www.mrchat.net/myblog/myblog/small-accidents-mean-big-trouble-for-supercollider.html Small Accidents Mean Big Trouble for Supercollider Image Scientists expect startup...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.