Hibernate Query without duplicates on two columns

Question

I have a table with 5 columns like this:

id, name, firstName, job, number

There are many records in this table. Now, imagine these records:

 - 1 Konan toto doctor  45
 - 2 Konan tata doctor  50
 - 3 Konan toto doctor  60
 - 4 simba popo police  44
 - 5 simba tata police  88
 - 6 pikar popo doctor  99
 - 7 simba popo doctor  72

now I want to find only record which job is doctor and get record with no duplicate in (name,firstName) (if we have many records with same name + lastName we return only one record , let say anyone)

the result will be

 - 1 Konan toto doctor  45
 - 2 Konan tata doctor  50
 - 6 pikar popo doctor  99
 - 7 simba popo doctor  72

the record with id 3 is removed because there is duplicate on (name,firstName) the others because we only need job = doctor

What is the Hibernate Query to get the desired results ?

Why do you have to do this with JPQL? It would be relatively easy with SQL... — Lukas Eder
– Lukas Eder, Commented Feb 19, 2019 at 10:11
You could also just use a simple group by with JPQL SELECT MAX(p.id), p.name, p.firstName, MAX(p.job), MAX(p.number) FROM Person p WHERE p.job = 'doctor' GROUP BY p.name, p.firstName — Christian Beikov
– Christian Beikov, Commented Feb 21, 2019 at 14:45
@ChristianBeikov: That will produce random combinations of id and number, which may not be present in the source data — Lukas Eder
– Lukas Eder, Commented Feb 21, 2019 at 14:58
You are right, something like SELECT p.id, p.name, p.firstName, MAX(p.job), MAX(p.number) From Person p WHERE p.id IN (SELECT MAX(p2.id) FROM Person p2 WHERE p2.job = 'doctor' GROUP BY p.name, p.firstName) is probably more appropriate — Christian Beikov
– Christian Beikov, Commented Feb 24, 2019 at 16:51

Lukas Eder · Accepted Answer · 2019-02-21 15:12:37Z

2

I'm not sure why this has to be done with JPQL, it seems rather difficult and produce an inefficient query that probably requires a self join. Here's a relatively simple Oracle SQL based solution

SELECT
  MAX(id)        KEEP (DENSE_RANK FIRST ORDER BY id) AS id,
  name,
  firstName,
  MAX(job)       KEEP (DENSE_RANK FIRST ORDER BY id) AS job,
  MAX(number)    KEEP (DENSE_RANK FIRST ORDER BY id) AS number
FROM t
WHERE job = 'doctor'
GROUP BY name, firstName

A more standards compliant solution using window functions:

SELECT
FROM (
  SELECT
    id, name, firstName, job, number,
    ROW_NUMBER() OVER (PARTITION BY name, firstName ORDER BY id) rn
  FROM t
  WHERE job = 'doctor'
) t
WHERE rn = 1

This is a special case of the top N per category problem. I recommend using a native query, which you can still map to entities if you really need to.

edited Feb 21, 2019 at 15:12

answered Feb 21, 2019 at 14:23

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thorben Janssen Over a year ago

I agree with Lukas. You should implement this query in SQL and not in JPQL. JPQL isn't powerful enough for these kinds of queries and JPA was intentionally designed as a leaky abstraction. You can use a native query to execute all kinds of SQL statements supported by your database: Query q = em.createNativeQuery("Enter Lukas' query here");

Collectives™ on Stack Overflow

Hibernate Query without duplicates on two columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related