Distinct results from Spring Data JPA Specification by one column

Question

I have the following table on database:

id	employee_id	title	created_at
1	10	title1	2022-10-10
1	10	title2	2022-10-11

I want fetch data by distinct on title column;

by sql I can do this:

select distinct on (employee_id) *
from contracts
order by employee_id, created_at desc

The size of result is one and it is correct:

id	employee_id	title	created_at
1	10	title2	2022-10-11

But when I want implement this by psecification, cannot distinct result by column:

Specification { root: Root<Contract>, query: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
            val firstNamePredicate: Predicate =
                    criteriaBuilder.equal(root.get<String>("employee").get<String>("id"), "1")
            query.distinct(true)
            criteriaBuilder.and(firstNamePredicate)
        }

This query return all rows that employee_id is equals 1.

I applied below changes, but result not changed:

query.select(root.get<String>("employee").get("id")).distinct(true)

How can I apply distinct on column?

Answer 1

Another solution in SQL is to use max function. In this question, I want to find employee by bigger created_at value. I can use follow SQL query:

select employee_id, max(created_at)
from contracts
group by employee_id;

Following the above answer, I used the below solution on Specification:

Subquery<Long> sub = q.subquery(Long.class);     
Root<Contract> subRoot = sub.from(Contract.class);     
sub.select(cb.max(subRoot.get("created_at")))
   .where(cb.equal(root.get("employee_id"), subRoot.get("employee_id")));
return cb.equal(root.get("created_at"), sub);