Hibernate Community • View topic

nickel · **Joined:** Mon Oct 06, 2003 2:20 pm **Posts:** 10

I have a that table have 3 fields

PK, goalID,ranking

it has the following data:

PK,Goalid, ranking
1,1,1
2,1,2
3,1,3
4,2,1
5,2,2
6,2,3
7,3,1
8,3,2
9,3,3
10,4,1
11,4,2
12,4,3
13,4,4

How can I using HQL query to get the result that return all all goals with the higest ranking?

I'd like to get a result like
3,1,3
6,2,3
9,3,3
13,4,4

thanks in advance

michael · **Posted:** Thu Feb 19, 2004 4:34 pm

Code:

select goal from Goal goal where ranking >= all (select goal2.ranking from Goal goal2)

or something like that. Only with subselects though.

emmanuel · **Posted:** Thu Feb 19, 2004 7:16 pm

No this won't work, you'll only have 13,4,4
Maybe

Code:

select goal from Goal goal where ranking >= all (select goal2.ranking from Goal goal2 and goal.id = goal2.id)

michael · **Posted:** Thu Feb 19, 2004 7:19 pm

Ah sorry, my query was wrong of course :( Take Emmanuels example.