HQL order by case

Question

I have a Entity :

class User{
    Long id;
    String group_name;
    User head;        
}

I have to get ordered users in order : order by group_name, if user is head then first (head means than head.id = id).

I make HQL Query : select u from User u order by u.group_name, case when u = head then 0 else 1; But it's not working;

In result I would like to have : (group_name : user)

g1 John (head)
g1 James
g1 Adam
g2 Brian (head)
g2 Martin

How to make correct HQL query ?

unclear question, give more detail and give table structure if possible. — Yogesh Prajapati, Oct 07 '14 at 11:26
http://stackoverflow.com/questions/9262413/hibernate-dynamic-order — Stefan, Oct 07 '14 at 11:36

Sushant Tambare · Answer 1 · 2014-10-07T11:44:31.837

0

Here is HQL query:

"select u.group_name from User u, User.head head where head.id=u.id order by u.group_name"

I didn't get what exactly from User you want since there is no column name in User class so that you can include in select part.

edited Oct 07 '14 at 11:44

answered Oct 07 '14 at 11:33

Sushant Tambare

526
2
9

Sorry, but I have a ordered list by group name – idmitriev Oct 07 '14 at 11:43
I your case I got just head users, but I want to get not head user as well – idmitriev Oct 07 '14 at 11:53
"select u.group_name from User u left join User.head head where head.id=u.id order by u.group_name" - Use this one.. it is left join which will give you both head as well as non head users as well. But then its like selecting whole User table itself. – Sushant Tambare Oct 07 '14 at 11:57
I want to have head user at first position !!! see example in my post. g1 John (head) Head on first position . – idmitriev Oct 07 '14 at 11:58

score 0 · Answer 2 · answered Oct 07 '14 at 12:29

I would do this without grouping, by using a case statement to create a second attribute that you can order on to establish if someone is head of a group. For example:

select name,group_name,case when head.id is not null then 1 else 0 end as h 
from User 
order by group_name asc,h desc

As you can see, I have used a case statement to create an attribute h which takes the value 1 when someone is head of a group, or 0 otherwise. Then you can order on that descending, which will mean the head of each group is listed before the other members of the group.

HQL order by case

2 Answers2