Hibernate throws "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" - SQL Server


#1

i am facing problem when i run my hibernate query for Mssql server

this is the hibernate query:

select 
	max(this_.created) as y0_, 
	this_.who_entity as y1_, 
	this_.what_entity as y2_, 
	this_.who_id as y3_, 
	this_.what_id as y4_, 
	this_.action as y5_, 
	this_.donotdisplay as y6_, 
	this_.created as y7_ 
from Activity 
	this_ 
where 
	:Tenant_Security.current_tenant=this_.tenant_id and 
	this_.deleted=0 and 
	(
		this_.donotdisplay is null and 
		this_.action is not null
	) 
group by 
	this_.created 
order by this_.created desc

And the error which i am facing is:

Column ‘Activity.who_entity’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

previously this was working in Mysql but Now i trying to execute in Mssql server its not working.can anybody help me out this.

Thankyou


#2

The GROUP BY clause should list all the non-aggregated columns that you want to project. And, most likely you want to ORDER BY max(this_.created), not this_.created.

So, your query should be changed to this:

select 
	max(this_.created) as y0_, 
	this_.who_entity as y1_, 
	this_.what_entity as y2_, 
	this_.who_id as y3_, 
	this_.what_id as y4_, 
	this_.action as y5_, 
	this_.donotdisplay as y6_, 
	this_.created as y7_ 
from Activity 
	this_ 
where 
	:Tenant_Security.current_tenant=this_.tenant_id and 
	this_.deleted=0 and 
	(
		this_.donotdisplay is null and 
		this_.action is not null
	) 
group by 
	this_.who_entity as y1_, 
	this_.what_entity as y2_, 
	this_.who_id as y3_, 
	this_.what_id as y4_, 
	this_.action as y5_, 
	this_.donotdisplay as y6_, 
	this_.created as y7_ 
order by 
	max(this_.created) desc