I have a table:
Employee Salary

Can somebody help me to express the following HQL request:
Find employees and for every employee the count of employees which have a salary superior to this employee ?

I made some searches but I do not manage to express this request.

Thanks !

I think this is fine:
select e.id, count(*) from Employee e, Employee e1 where e.salary < e1.salary and e.id <> e1.id group by e.id

If I have four Employees:
E1 1000
E2 2000
E3 3000
E4 4000

The query:
select e1, count(*) from Employe as e1, Employe as e2 where e1.salaire > e2.salaire group by e1
gives only:
E2 2000 1
E3 3000 2
E4 4000 3

But the query
select e1, count(*) from Employe as e1, Employe as e2 where e1.salaire >= e2.salaire group by e1
gives:
E1 1000 1
E2 2000 2
E3 3000 3
E4 4000 4
And that is really what I wanted (the rank of the Employee for the salary)

Thanks for your help

I forget to mention that those queries work with MySQL but not with Derby, (probably group by not allowed with count)

See this bug report:
http://opensource.atlassian.com/project ... e/HHH-1615

The workaround is this HQL:

In the group by clause list all the properties of the Employee entity.

I had taken the example of Employee to explain my problem. Here is my real request:

select new galland.planning.backing.SeanceInfo(s1, count(*))
from Seance as s1 , Seance as s2
where s1.jour >= :from
and s1.jour <= :to
and s1.enseignant = :enseignant
and s1.enseignement = s2.enseignement
and (s1.jour > s2.jour or s1.jour = s2.jour and s1.heure >= s2.heure)
group by s1
order by s1.jour, s1.heure")
.setDate("from", from)
.setDate("to", to)
.setEntity("enseignant",getEnseignant())
.list();

It is for a university schedule and It finds the sessions (Seance) between two dates from and to for a givent teacher (Enseignant) with for every session the rank (by day and hour) in the course (enseignement).
It works, Thanks to HQL !

Thanks again for your help
Dominique