Hibernate and rownum in inner query

Question

I have the following query in oracle that I want to use in hibernate but cannot work out how to use the rownum variable in an inner query. The query is as follows:

select emp.prof_key, emp.prof_display_name
from empinst.emp emp
where emp.prof_key IN (select x.object_key
                   from (select event.object_key as object_key
                         from empinst.eventlog event
                         where event.event_name = 'profiles.created'
                         and event.event_source = 'Profiles'
                         order by event.created desc) x
                   where rownum <= 10)

The only way that I can work out how to do it is to break the query into two parts but I assume there must be a more efficient way to do it in hibernate.

Thanks in advance.

Michael.

Answer 1

Hi Michael,

your query will not filter the 10 most recent records. It will return 10 random records (Random as in unreliable order) since there is no ORDER BY clause.

I'm not familiar with the limitations of hibernate but as with all limiting tools I'm pretty sure you can work around them with a well-thought view.

For example, this view will contain a rank column you can use to filter the 10 most recent record for any combination of (event_name, event_source) based on an ordering column event_date:

CREATE VIEW eventlog_rank_v AS 
SELECT e.*, 
       row_number() OVER (PARTYTION BY e.event_name, 
                                       e.event_source 
                          ORDER BY e.event_date DESC) event_rank
  FROM empinst.eventlog e;