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?
2
Answers
This error occurs because SQL requires columns in the ORDER BY clause to also be in the SELECT list when using DISTINCT. When DISTINCT is applied, the database sorts on the fields selected in the projection, but entity.auditTimestamp isn’t included in the SELECT list, causing the error.
Here are some approaches to solve this issue:
You can add entity.auditTimestamp to the SELECT list within the MyDTO constructor. This way, it becomes available for the ORDER BY clause. However, if you don’t want auditTimestamp to be part of MyDTO, you might consider modifying MyDTO to ignore or handle it separately.
Another approach is to use a subquery. First, fetch the distinct IDs based on the fields you want and then order the result by auditTimestamp in the main query. Here’s an example:
This might require adjusting the query based on your entity structure, but it can allow you to keep the DISTINCT logic while ordering.
Fetch non-distinct results using ORDER BY and then eliminate duplicates in Java. This might be less efficient if there are many rows, but it can work well for smaller datasets.
Solution 1: Sort in Memory
If the result set is not too large, you can fetch the distinct records without ordering and then sort them in memory within your Java code.
Solution 2: Use Native Query
If the above solutions are not viable, you can consider using a native SQL query to achieve the exact query you want.
Solution 3: Use @QueryHints to Influence SQL Generation (Hibernate Specific)
If you are using Hibernate, you can use @QueryHints to customize SQL generation behavior. However, it may not fully resolve the ORDER BY issue with DISTINCT, as it depends on your database’s specific implementation.