I have the query shown below which throws an error that "JudgeName" is an
invalid column name? Is using an alias not allowed here?

Wayne

=================  code  ==============
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By JudgeName, Caption

SQL BASIC RULE: YOU CAN'T USE ALIAS IN GROUP BY
Method 1:
SELECT (Names.LastName + ',' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By (Names.LastName + ',' + Names.FirstName), Caption

Method 2
SELECT a.JudgeName from
(SELECT (Names.LastName + ',' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By Caption ) a GROUP BY a.JudgeName

Regards
R.D
Show quoteHide quote

Wayne,

Unfortunately not. You can use the expression that makes up JudgeName. For
example,

SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By (Names.LastName + ', ' + Names.FirstName), Caption

- or -

SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By Names.LastName, Names.FirstName, Caption

Hope this helps,

Yosh

Show quoteHide quote

Locally, the GROUP BY is performed before the SELECT list, hence you cannot group by a column alias.
Either repeat the expression in GROUP BY or use a derived table.

Show quoteHide quote

....
GROUP BY LastName, FirstName, Caption

Unknown Columns!
Simple query?
adding 140,000 records requires index rebuild. make sense?
Need advice with a query
Index Tuning Wizard