Bilal DEKAR Bilal DEKAR - 3 months ago 16
Java Question

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
.

acm acm
Answer

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.

Comments