18

I need to take hql that is currently :

select distinct a from Asset as a where ...

and change it to

select new com.org.AssetDTO(a.id, a.address, a.status) from Asset as a where ...

My problem is with the distinct keyword. Where does it belong in an hql query where you're using the new Object query type. One thought was to use a sub-select and have my distinct there. I've tried adding distinct a.id but that doesn't work.

dstarh
  • 4,976
  • 5
  • 36
  • 68

1 Answers1

29

Ok for anyone interested the proper syntax is 

select distinct new com.org.AssetDTO(a.id, a.address, a.status) from Asset as a where ...
dstarh
  • 4,976
  • 5
  • 36
  • 68
  • 2
    Does this mean the result of your query, is not affected by the AssetDTO equals() method? – Leon Nov 01 '17 at 13:15
  • Why would the results of the query be affected by the equals() method? Perhaps I'm not understanding your question – dstarh Nov 01 '17 at 15:34
  • `distinct new com.org.AssetDTO` is equivalent of `distinct a from Asset` and not equivalent of `distinct(a.id, a.address, a.status)`. All the fields of the entity are used in selecting the DISTINCT? – Leon Nov 01 '17 at 16:04
  • 1
    select distinct new com.org.AssetDTO(a.id, a.address, a.status) from Asset as a is the same as a normal sql select statement of select distinct a.id, a.address, a.status from Asset a and then providing a RowMapper to turn that into an array of DTO's, this syntax is/was the HQL equiv. of that – dstarh Nov 02 '17 at 01:04
  • 1
    but what if i need more columns then i need in distinct clause? ex, if i want distinct only on (a.id, a.address) but i need the data also of a.status – Andrea Scarafoni Nov 06 '20 at 11:24