Limit in subquery

rixis · **Joined:** Mon Aug 09, 2004 8:12 am **Posts:** 4

Hi,

I would like to use LIMIT 1 in subquery, but Hibernate creates from my query:

select o from Cobject o join fetch o.objectDatas od join o.keyalias ka where od.enabled = :objectDataEnabled and od = (select od2 from ObjectData od2 join od2.cobject o2 where
(o2 = o) limit 1) ORDER BY o.orderCount DESC

this one (only relevant part):

...objectdata1_."ID"=(select objectdata4_."ID" from "OBJECT_DATA" objectdata4_, "OBJECT" cobj
ect5_ where objectdata4_."OBJECT_ID"=cobject5_."ID" and (((cobject5_."ID"=cobject0_."ID" )limit 1 )))...

and it is not valid SQL (limit 1 is in 'wrong' place).

Hibernate version:
2.1.6

muirwa · **Joined:** Thu May 06, 2004 4:06 pm **Posts:** 25

Interesting, I just ran into the same problem:

Code:

"FROM Volume v WHERE v.type.id=4 AND v.size.id=4 LIMIT 1"

which generates SQL:

Code:

select db_volume0_.id as x0_0_ from volume db_volume0_ where (db_volume0_.type_id=4 )AND(db_volume0_.size_id=4 LIMIT 1 )

(Also, adding parentheses around the "WHERE" conditions does not help).

christian · **Posted:** Wed Oct 13, 2004 12:33 pm

HQL has no "limit" keyword.

rixis · **Joined:** Mon Aug 09, 2004 8:12 am **Posts:** 4

christian wrote:

HQL has no "limit" keyword.

no workaround? Its somewhat "limiting" :D

sgwood · **Posted:** Tue Oct 26, 2004 4:47 pm

Though HQL does not officially have a limit keyword, I have unwittingly been successful in using limit in my queries. The secret (at least for Postgres - don't know about other DBs) is:

from a
where a.b = c
order by xxx
limit 1

This is the preferred way to get a max or min in Postgres, because max and min performance in Postgres (7.X and previous) could be better.

Sherman