Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?
Tags:

2

Answers


  1. 0

    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:

    1. Include auditTimestamp in the SELECT list
      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.
    @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, entity.auditTimestamp) "
     + "FROM Entity entity "
     + "LEFT JOIN User user ON entity.userId = user.id "
     + "ORDER BY entity.auditTimestamp DESC")
    1. Use a Subquery

    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:

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

    This might require adjusting the query based on your entity structure, but it can allow you to keep the DISTINCT logic while ordering.

    1. Fetch Distinct Results in Java
      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.
    @Query("SELECT 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")
List<MyDTO> results = repository.findAll();
List<MyDTO> distinctResults = results.stream().distinct().collect(Collectors.toList());
    Login or Signup to reply.
  2. 0

    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.

    @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")
List<MyDTO> findDistinctResults();

// Sort the result in memory
List<MyDTO> sortedResults = findDistinctResults().stream()
    .sorted(Comparator.comparing(MyDTO::getAuditTimestamp).reversed())
    .collect(Collectors.toList());

    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.

    @Query(value = "SELECT DISTINCT e.id, u.id AS userId, u.email, r.display_value AS roleDisplayValue, "
             + "p.id AS practiceId, p.name AS practiceName, e.inactive_date, e.type, e.status, e.document_id "
             + "FROM entity e "
             + "LEFT JOIN user u ON e.user_id = u.id "
             + "ORDER BY e.audit_timestamp DESC", nativeQuery = true)
List<MyDTO> findDistinctResultsNative();

    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.

    @QueryHints(@QueryHint(name = "hibernate.query.passDistinctThrough", value = "false"))
@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")
List<MyDTO> findDistinctResults();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search