CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
kingrichard2005
kingrichard2005

Reputation: 7269

TSQL error in GROUP By clause

I have the following query on a populated Table variable that is supposed to return rows or each with the most recent date:

SELECT t.ID
    ,t.Column_2
    ,t.MaxDate AS [End Date Time]
    ,t.Column_3
    ,t.Column_4
    ,t.Column_5
FROM (
    SELECT ID
        ,Column_2
        ,MAX(EndDateTime) AS MaxDate
        ,Column_3
        ,Column_4
        ,Column_5
    FROM @MyTable
    GROUP BY Column_2
    ) t
INNER JOIN @MyTable o ON t.ID = o.ID
    AND t.MaxDate = o.EndDateTime

I keep getting the error: Column '@MyTable.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Not sure what the problem is as I'm including a GROUP BY clause in the inner select statement, do I need one outter select query as well? Also, how can a modify this to ORDER BY EndDateTime DESC in the final return result?? Thank you in advance.

UPDATE:

Thank you all for your feedback, I corrected the error and learned something new. THanks.

Upvotes: 0

Views: 671

Answers (4)

Carth
Carth

Reputation: 2343

When you define a group by all of the items in your select must either be included in the group by clause or included in an aggregate so the server will know what to do with the grouped values. In your inner select you would need to do something like this:

   SELECT ID
    ,Column_2
    ,MAX(EndDateTime) AS MaxDate
    ,Column_3
    ,Column_4
    ,Column_5
FROM @MyTable
GROUP BY ID
    ,Column_2
    ,EndDateTime
    ,Column_3
    ,Column_4
    ,Column_5

OR

SELECT SUM(ID)
    ,Column_2
    ,MAX(EndDateTime) AS MaxDate
    ,SUM(Column_3)
    ,SUM(Column_4)
    ,SUM(Column_5)
FROM @MyTable
GROUP BY Column_2

Without providing either an aggregate or including the additional columns in the group by their behavior would be undefined.

Upvotes: -1

Kiley Naro
Kiley Naro

Reputation: 1769

The error is coming from the inside-most SELECT query. It is telling you that you can't apply an aggregate field on one column, group by a second column, and then do nothing with the other 4. Try this instead (results may vary, depending on what you are actually trying to produce for a result):

SELECT t.ID
    ,t.Column_2
    ,t.MaxDate AS [End Date Time]
    ,t.Column_3
    ,t.Column_4
    ,t.Column_5
FROM (
    SELECT ID
        ,Column_2
        ,MAX(EndDateTime) AS MaxDate
        ,Column_3
        ,Column_4
        ,Column_5
    FROM @MyTable
    GROUP BY ID,Column_2,Column_3,Column_4,Column_5
    ) t
INNER JOIN @MyTable o ON t.ID = o.ID
    AND t.MaxDate = o.EndDateTime

Upvotes: 0

Lamak
Lamak

Reputation: 70638

You need to include all the columns that you are not aggregating. I think you should do the following:

SELECT o.ID
    ,o.Column_2
    ,t.MaxDate AS [End Date Time]
    ,o.Column_3
    ,o.Column_4
    ,o.Column_5
FROM (
    SELECT ID
        ,MAX(EndDateTime) AS MaxDate
    FROM @MyTable
    GROUP BY ID
    ) t
INNER JOIN @MyTable o ON t.ID = o.ID
    AND t.MaxDate = o.EndDateTime

Upvotes: 1

JNK
JNK

Reputation: 65147

You need to either aggregate MAX(), MIN(), AVG() or GROUP BY EVERY field in the select statement.

You don't GROUP or aggregate ID, Column_3 - Column_5 which is causing the issue.

Upvotes: 3

Related Questions