oracle - GORM - select max records with order and lock - ORA-02014 -

how accomplish following query gorm?

select *  t id in  (     select id     (         select *         t         x null         order y desc         )     rownum <= 1 ) update; 

the method call i'm trying looks this:

t.findbyxisnull(sort: "y", order:"desc", lock: true) 

however, got following error (oracle 11gr2): ora-02014: cannot select update view distinct, group by, etc.

the reason believe it's failing because hibernate converting following query:

select * (     select *     t     x null     order y desc     ) rownum <= 1 update; 

this query trying apply update directly on clause limiting rownum. wrapper select statement needed apply update, in example @ top (and discussed here: how solve ora-02014: cannot select update view distinct, group by). how can accomplished?

update1

looks bug in gorm/hibernate in generating query when both order by , rownnum <= ? specified. both of following work individually:

t.findbyxisnull(sort: "y", order:"desc") t.findbyxisnull(lock: true) 

but t.findbyxisnull(sort: "y", order:"desc", lock:true) fail ora-02014 error. fix hibernate generate sql described @ top of post, wraps lock outer select statement. however, there may workaround i'm not aware of.

afaik findby* not support pagination , order parameters because return first matching result.

if want use sorting have use findallby* , pick first row locking.

using findby try

//to avoid infinitesimal chance of dirtiness between fetching , locking. def t = t.findbyxisnull([lock: true]) //round-about unliked way  def t = t.lock(t.findbyxisnull()?.id)   //or easier def t = t.findbyxisnull() t.lock() 

using findallby pagination params:

def t = t.findallbyxisnull(sort: "y", order:"desc", max: 1, lock: true) 

untested oracle db