CODEWITHSUNDEEP
javajpaselectdistinctjpql
Bilal Dekar
Bilal Dekar

Reputation: 3966

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.

Upvotes: 1

Views: 4270

Answers (2)

acm
acm

Reputation: 2120

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.

Upvotes: 4

Anas Lachheb
Anas Lachheb

Reputation: 441

Try this :

DELETE from Client c
Where c.name IN (SELECT DISTINCT cl.name
                 FROM Client AS cl
                 WHERE cl.country='****')

But pay attention to your persistence context to avoid corrupt data.

Upvotes: 1

Related Questions