Hi, I am losing my mind with HQL as it seems to have a few limitations.
For example I have searched far and wide to find an answer to this problem but have failed miserably!

Say i have a simple table with table with 3 columns: Person(id,name,number)
Now i simply want to get all the information of the person with the longest number. So my HQL looks like this:



This will obviously get a group by error from my PostgreSQL database.

Now I can do this in SQL using a join subselect in the from clause but since i cant do that in hql, im lost. I feel like there is a simple solutin but i cant find it!
PLEEEASE HELP!

What about this, it is not join subselect but I think it gets the data you need

14.13. Subqueries

hehe thanks pepelnm, that was actually a simplified version of the statement i was actually using which had some joins, so i couldnt use your solution. but i changed my tables around so i was able to use hql to get the result i wanted, which kinda sux but anyways thanks again and if this same problem comes up again i would definately love to see your soluton for it...
cheears :)

ok I've run into another problem that i could have solved using join subselects in from clause in sql but for the life of me i cant figure out how to do this in hql.
This is a much better representation of what i was trying to do earlier in the thread.

I have 2 tables linked by a many-many relationship through another table.
Table1:Callrate
Table2:Prefix
Table3:Callrateprefix

Now when i reverse engineer these three tables in hibernate it creates pojo etc for only callrate and prefix and a relationship Set for eachother. (i hope im making sense my hibernate/db lingo is very immature).

It's a quite simple relationship between callrate and prefix. Every prefix has many callrates. (and for a different purpose callrates can also have many prefixes but this isnt important in this case)

So what i am trying to do is get the cheapest rate for every prefix and some rate details also.

To get the cheapest rate for every prefix i use:

But i cant get any other callrate info along with the result like callratename etc. because ill have to put it in the group by clause and that just screws up the result.

I tried and tried and researched and tried some more (and screamed WHYYY OH WHYYY are there no join subselects!) and the best thing i could come up with was:


Which works when theres no joins but for some reason just returns the full list of rateid/prefixid matchs and there rate. I would love to know how to solve this because it will solve a bunch of other problems like this that im having to do weird things to solve. Thanks in advance :)

i thought this would be a bit harder :s
anyone???

This is reeally affecting my work, can anyone please help with this problem.
It would be very much appreciated!
Happy Easter!

just checking if anyones replied... so im gona take the lack of response as a sign that it cant be done. thought post to let anyone who finds this thread to know that i still dont have an answer to this and doesnt look like i will. so sad hibernate. back to sql queries i guess.