CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Dantes
Dantes

Reputation: 2891

GROUP-BY expression must contain at least one column that is not an outer reference

i have this query:

SELECT
SolutionName    -- Solution_NAM
from  Table_View
Group By 1

where Table_View is of course a view. I am gettign this error message:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Can you help me resolve this? It´s inside Ms SQL Server 2008

Upvotes: 7

Views: 14985

Answers (2)

user7772648
user7772648

Reputation:

Martin's statement about GROUP BY is correct, although you >can< definitely use a column ordinal instead of a column name in an ORDER BY, in various versions of SQL (I'm thinking TSQL, which is ISO standard in most core SQL syntax).

But it's wise practice not to use column ordinals in ORDER BY statements, even though they do work. Why? If the query is a part of a stored proc, and the table schema is altered by adding a column to the original table (or view), there is no guarantee that the original column ordering will be maintained by the change. Which would mean that you could be ordering by an unintended and meaningless column, which then breaks your application.

It's far better practice to use column names rather than ordinals every time (although like count(*), don't get hung up on it if it's ad-hoc querying for your own use that will never see the light of day in production).

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

you cannot give Group by 1

try

SELECT
SolutionName    -- Solution_NAM
from  Table_View
Group By SolutionName

Upvotes: 5

Related Questions