How to get the distinct values in a column as well as total count using spring jpa pagination-CodePudding

I am trying to select list of unique values of column C and the corresponding count as well. My code is as following

    @Query(value = "SELECT DISTINCT t.C FROM table t WHERE t.param = :param order by t.C",
        countQuery = "SELECT count(DISTINCT t.C) FROM table t WHERE t.param = :param")
    Page<String> findUniqueWithCountPagination(Pageable pageable,
        @Param("param") String param);

I am getting the error ORDER BY items must appear in the select list if SELECT DISTINCT is specified. When I enable the show-sql property, I noticed that Spring is adding another column U automatically to the order by filter. U is a timestamp column in table t.

How to remove the unnecessary field U in the order by? if that is not possible, is there any other way to write the query for my requirement?

CodePudding user response：

I only needed the unique values in column C and it's count so I applied the sort over C itself and removed it from the query. order by is auto applied during execution. Code snippets are as following:

 Pageable pageable = PageRequest.of(filterOption.getPageNo(), filterOption.getPageSize(), Sort.Direction.DESC, C);


 // repository class
 @Query(value = "SELECT DISTINCT t.C FROM table t WHERE t.param = :param",
 countQuery = "SELECT count(DISTINCT t.C) FROM table t WHERE t.param = :param")
 Page<String> findUniqueWithCountPagination(Pageable pageable,
 @Param("param") String param);