Hibernate Select distinct with order by another table

Question

I know there are many topics around this, but I can't find a solution to my problem. I know if I use "distinct" and "order by" with hibernate, the column from the order by has to be in the select clause. The other articles just say that the column must be in select, but I haven't found an example how this is possible to achieve.

This is my Class:

@Entity
@Table(name = "DANCE")
public class Dance implements Serializable {

@Id
private int tanzid;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "SID")
private School school;

@Basic
@Column(name = "titel")
private String titel;

(getter setter...)

My (not working) query:

SELECT distinct d FROM Dance d order by d.school.schoolname

If I do order by title for example it is working. I have tried to set the schoolname in the select clause:

SELECT distinct d, d.school.schoolname FROM Dance d order by d.school.schoolname

but then hibernate is complaining that the return type of the query isn't from type "Dance.class"

How can I achieve an order by schoolname.

Thank you

you use @ManyToOne means there are more one table Dance and school. when you order by title its worked because title is in dance table. where schoolname not in dance table you have to join two tables dance and school then order by schoolname. — Akash Shah, Mar 11 '19 at 09:39
@SHAHAKASH Also if I do an inner join on table school it isn't working — Paul, Mar 11 '19 at 11:21

score 0 · Accepted Answer · answered Mar 11 '19 at 09:33

You would have to select distinct rows with regard to one column. This is unfortunately not the part of sql standard, so in jpql or hql this cannot be done. You would have to use vendor - specific syntax and native query to achieve this. Here is an example in postgres.

Hibernate Select distinct with order by another table

1 Answers1