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

Question

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. :)

Answer 1

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

Answer 2

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