Search code examples
javajpaselectdistinctjpql

Use select distinct in JPQL and retrieve all columns

I'm working with JPQL, I want to remove all the duplicated rows using DISTINCT, and at the same time retrieve all the columns in a table, I wrote something like this: 

SELECT DISTINCT cl.name, cl.age 
FROM Client AS cl
WHERE cl.country='****'

This query returns just the two columns name and age.

Solution

  • Assuming you have a unique id you could write your query to use GROUP BY as follows:

    SELECT client FROM Client client
WHERE client.id IN (
    SELECT MIN(c.id)
    FROM Client c
    WHERE c.country='****'
    GROUP BY c.name, c.age
)

    You should not retrieve all the fields of Client because you should not select non-aggregated fields.