invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

biswetbf 于 2023-02-21 发布在其他

关注(0)|答案(4)|浏览(419)

I have searched about this error and amended my query and used my order by column in aggregate function as well. May be due to lesser knowledge of sql I am unable to catch exact meaning of this error.

I have following columns in my table:

[id]
[post_id]
[user_id]
[photo_id]
[photo_group_id]
[album_id]

my query:

SELECT TOP 3 MAX(share.id) as share_id, share.user_id 
FROM share  
WHERE share.post_id = 5468   
GROUP BY share.user_id 
ORDER BY share.id desc

I am using order by column in aggregate function already still it showing

Column "share.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Any help will be appreciated. :)

sql-server

来源：https://stackoverflow.com/questions/33580104/invalid-in-the-order-by-clause-because-it-is-not-contained-in-either-an-aggregat

4条答案

按热度按时间

f4t66c6m1#

The column isn't included in the ones SELECTed... I would change the ORDER BY to be either the aggregated column:

ORDER BY MAX(share.id) DESC

or the index of the column in the select:

ORDER BY 1 DESC

赞(0）回复(0）举报 2023-02-21

xwmevbvl2#

share.id is not valid as you used an alias in your select statement.

Try using share_id instead like this:

SELECT TOP 3 MAX(share.id) as share_id, share.user_id 
FROM share  
WHERE share.post_id = 5468  
GROUP BY share.user_id
ORDER BY share_id DESC

赞(0）回复(0）举报 2023-02-21

ut6juiuv3#

ORDER BY happens after SELECT . Because you only have MAX(share.id) in the SELECT , ORDER BY can only use what's already selected. Your 'share.id' is simply not selected for ORDER BY to use.
The other issue is you have MAX(share.id) and share.user_id on the same level. They have to be better defined by GROUP BY and aggregate functions.