Column is invalid in the ORDER BY clause with JPA and SQL Server-CodePudding

When I searched this error, found many results, but all are answered are at database (SQL Server) level. But, I am getting this error with JPA query( Simple count query with order clause) and SQL Server database. This code is working fine with MySQL database.

Code:

FilterBuilder builder = new FilterBuilder("FROM RuleChangeLog A  ");
{
  builder.startWhere();
  builder.checkAndAdd1(filters[0], "and A.action LIKE ?", filters[0] );
  builder.checkAndAdd1(filters[1], "and A.ruleName like ?", filters[1] );
  builder.checkAndAdd1(filters[2], "and A.updatedBy like ?", filters[2] );
}
TypedQuery<Long> countQuery = em.createQuery("select count(A) "   builder.getJPQL(), Long.class);
        builder.setParameters(countQuery);

Getting below error when I run countQuery.getSingleResult() method.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column "rule_change_log.update_time" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Below is the converted database query for the same:

select count(rulechange0_.id) as col_0_0_ from rule_change_log rulechange0_ where 1=1 order by rulechange0_.update_time DESC

CodePudding user response：

As @Dale K suggested, After removing the order clause in query, it worked.