JPQL: getting whole entities on a distinct column

If you want distinct entities in JPQL you would normally write something like this:

SELECT DISTINCT(l) FROM Letter l WHERE l.name=:name;

But this will do a DISTINCT on the whole Entity. This will also fail on Oracle DB if your Entity contains a CLOB. What if you really want to do a DISTINCT on a field, for example:

SELECT DISTINCT(l.id) FROM Letter l WHERE l.name=:name;

Unfortunately, this only returns an array of ID fields. If you want to retrieve the full entities and do a DISTINCT on a field the final query looks like:

SELECT ll FROM Letter ll WHERE ll.id IN (SELECT DISTINCT(l.id) FROM Letter l WHERE l.name=:name);

Cen
GitHub
Eurobattle.net
Lagabuse.com
Bnetdocs