[SQL] How to Resolve ORDER BY Clause Error with DISTINCT in JPA Query Using Spring Data?

I'm working with a JPA query in a Spring Data repository that fetches data using DISTINCT and an ORDER BY clause. The query works without DISTINCT, but adding it results in the following error:

Expression #1 of ORDER BY clause is not in SELECT list, references column 'entityName.auditTimestamp' which is not in SELECT list; this is incompatible with DISTINCT

Here is the query I'm using:


@Query("SELECT DISTINCT new com.example.dto.MyDTO(entity.id, user.id, user.email, role.displayValue, practice.id, practice.name, entity.inactiveDate, entity.type, entity.status, entity.documentId) "
+ "FROM Entity entity "
+ "LEFT JOIN User user ON entity.userId = user.id "
......
+ "ORDER BY entity.auditTimestamp DESC")


What is the best way to handle this situation?

• Is there a way to keep DISTINCT and ORDER BY entity.auditTimestamp without including auditTimestamp in the SELECT list? Or are there alternative approaches to achieving the same result in JPA?

Continue reading...