Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1727
  • Last Modified:

Error: Column "tablename.columnName" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Hi experts,

I am completely stumped by this sql error. I am running MS SQL Server 2005. There is one instance running on our test server and another instance running on our staging server.
The query is as follows:
      SET NOCOUNT ON
      DECLARE @stationID as int
      DECLARE @startDate as datetime
      DECLARE @endDate as datetime
      
      set @stationID = 3
      set @startDate = '01-Feb-2008'
      set @endDate = '12-Feb-2008'

      DECLARE @tblTicketSummary TABLE
            (ticketID INT, ticketPrice MONEY,
            totalNumberSold INT, totalPrice MONEY)

      INSERT INTO @tblTicketSummary
            SELECT
                  ticketID, ticketPrice,
                  totalNumberSold, totalPrice
            FROM
                  tblShiftBalanceTicketSummary
            WHERE
                  summaryDate >= @startDate
                  AND summaryDate <= @endDate
                  AND stationID=@stationID

      SELECT
            TMP.ticketID, MIN(T.ticketCode) AS ticketCode,
            MIN(TG.groupName) AS groupName, MIN(TG.groupID) AS groupID,
            MIN(T.ticketDescription) AS ticketDescription, SUM(TMP.totalNumberSold) AS sold,
            TMP.ticketPrice, SUM(TMP.totalPrice) AS salesTotal
      FROM
            @tblTicketSummary TMP
                  INNER JOIN tblTickets T
                        ON T.ticketID = TMP.ticketID
                  INNER JOIN tblTicketCodes TC
                        ON T.ticketCode = TC.ticketCode
                  INNER JOIN tblTicketGroups TG
                        ON TC.groupID = TG.groupID
      GROUP BY
            TMP.ticketID,TMP.ticketPrice
      ORDER BY
            TG.groupID, TMP.ticketID
      SET NOCOUNT OFF

When I run this on the test server it works perfectly!!! No error and produces the desired results.

However, when I run this exact same query on the staging server with the exact same input parameters, it gives this error:
Msg 8127, Level 16, State 1, Line 25
Column "tblTicketGroups.groupID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

I can't for the life of me figure out why it would give this error!! Can someone please help me?

Thanks.

0
maloriopolium
Asked:
maloriopolium
  • 4
  • 3
1 Solution
 
Anthony PerkinsCommented:
The staging table is returning the correct answer.  groupID has to be in the GROUP BY clause in order that you can include it in the ORDER BY.

Make sure that the test server has the same version and Service Pack as the staging server.
0
 
Anthony PerkinsCommented:
Having said that you should be able to do this:
      ORDER BY
            groupID, TMP.ticketID

Where groupID is the alias for MIN(TG.groupID)
0
 
maloriopoliumAuthor Commented:
Thanks for your reply acperkins.

So the query itself was actually wrong all along??!!
I might also add that we recently upgraded from MS SQL Server 2000 to 2005. So I can only presume that this query worked on SQL Server 2000 as well. So I am surprised that this problem only came up until now. I wonder what kind of quirk was making this query work in the first place?

I shall check the versions on both staging and test to see if there are any differences.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
>>I wonder what kind of quirk was making this query work in the first place?<<
I beleive you have hit the nail on the head.

SQL Server 2000 allowed you to do this:
ORDER BY
            TG.groupID, TMP.ticketID

But in fact, I suspect is actually doing an ORDER BY on the alias groupID and not TG.groupID. It is very easy to prove, just change this on your test server (the one that "works"):
MIN(TG.groupID) AS groupID,

To:
MIN(TG.groupID) AS quirk,

And see what happens.  I suspect you will get the same error.

There is a very similar difference between 2000 and 2005 when using UNION'd statements. SQL Server 2000 allowed you to have something like TG.groupID when in fact it was just using the alias groupID, SQL Server 2005 correctly does not allow it any more.
0
 
Anthony PerkinsCommented:
>>So I can only presume that this query worked on SQL Server 2000 as well. <<
It was syntactically correct as per SQL Server 2000.  I suspect it was not doing what you thought it was doing, there is no way it could.
0
 
maloriopoliumAuthor Commented:
Thanks acperkins. I have accepted your solution. Also I have just digged up some upgrade notes that were sent to me when the upgrade was performed. (Guess I should have read them!!)

The upgrade advisor correctly picks up the problem:

Upgrade Advisor detected the use of noninteger constants in the ORDER BY clause of one or more statements. Noninteger constants are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 and earlier. Noninteger constants in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90.

When you upgrade to SQL Server 2005, user databases maintain their compatibility mode. Before you change the database compatibility mode to 90, modify statements that use noninteger constants in the ORDER BY clause to use a column name or column alias, or a nonnegative integer representing the position of the name or alias in the select list.

The following example uses a noninteger constant in the ORDER BY clause of a query and an equivalent query that uses a valid ORDER BY expression.

Not Valid
USE pubs
SELECT au_id
FROM authors
ORDER BY 'a'

Valid
USE pubs
SELECT au_id
FROM authors
ORDER BY au_id

In SQL Server 2005, column aliases in the ORDER BY clause cannot be prefixed by the table alias.
For example, the following query executes in SQL Server 2000, but returns an error in SQL Server 2005:
USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY p.l
The SQL Server 2005 Database Engine does not match p.l in the ORDER BY clause to a valid column in the table.
Exception
If the prefixed column alias that is specified in the ORDER BY clause is a valid column name in the specified table, the query executes without error; in SQL Server 2005, the semantics of the statement might be different. For example, the column alias (id) specified in the following statement is a valid column name in the sysobjects table. In SQL Server 2000, when the statement executes, the CAST operation is performed after the result set is sorted. This means the name column is used in the sort operation. In SQL Server 2005, the CAST operation occurs before the sort operation. This means the

0
 
maloriopoliumAuthor Commented:
continued from above comment.........

id column in the table is used in the sort operation and returns the result set in an unexpected order.
SELECT CAST (o.name AS char(128)) AS id
FROM sysobjects AS o
ORDER BY o.id;
Corrective Action
Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause in either of the following ways:
Do not prefix the column alias in the ORDER BY clause, if possible.
Replace the column alias with the column name.
For example, both of the following queries execute without error in SQL Server 2005:
USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY l

USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY p.LastName

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now