TOP-N Query possible with HQL?

nweddle · **Posted:** Mon Aug 02, 2004 9:59 pm

Here is my Oracle TOP-N Query:

Code:

SELECT * FROM (SELECT * FROM ul_common_log_event WHERE application_name = 'Configuration' ORDER BY cle_id DESC) WHERE ROWNUM <= 500

This query returns the latest 500 records, instead of the first 500.

I tried this in HQL:

Code:

from CommonLogEvent cle (from CommonLogEvent cle where cle.applicationName = 'Configuration' ) WHERE ROWNUM <= 500

I got this error from Hiberante:

net.sf.hibernate.QueryException: unexpected token: ( [from CommonLogEvent cle (from CommonLogEvent cle where cle.applicationName = 'Configuration' ) WHERE ROWNUM <= 500]

Is this possible to do in HQL?

Thank you!

michael · **Posted:** Tue Aug 03, 2004 4:21 am

session.createQuery("from CommonLogEvent cle where cle.applicationName = 'Configuration' ").setMaxResults(500).list()

nweddle · **Posted:** Tue Aug 03, 2004 10:48 am

Thanks for the reply.

The setMaxResults(500) returns the first 500 records found. The purpose of the TOP-N query is to return the latest 500 records.

I need the latest 500, not the first 500. Is this possible with HQL?

Thanks!

christian · **Posted:** Tue Aug 03, 2004 10:55 am

Order By

gsmith53 · **Joined:** Tue Aug 03, 2004 12:18 pm **Posts:** 11

Does this work in hibernate version 2.0? We can't seem to get it to limit the number of returned rows.

gsmith53 · **Joined:** Tue Aug 03, 2004 12:18 pm **Posts:** 11

I'm sorry. does it work in version 2.1.4? That's the version that we are using.

christian · **Posted:** Tue Aug 03, 2004 12:21 pm

"My car doesn't work."

gsmith53 · **Joined:** Tue Aug 03, 2004 12:18 pm **Posts:** 11

I don't understand your reply Christian. Are you saying that it is broken in version 2.1.4?

christian · **Posted:** Tue Aug 03, 2004 12:38 pm

I was turning your statement into the equivalent for a car mechanic. What do you think would be the reply if you say this to a car mechanic?

(Man, is it the heat or what...)

gsmith53 · **Joined:** Tue Aug 03, 2004 12:18 pm **Posts:** 11

Christian, I don't need a smart ass remark. All I'm asking is whether you claim that setMaxResults(500) is in fact supported in version 2.1.4. If you say yes, then I won't bother you. We'll figure out why it's not working for us. But my guy says that the current hibernate code he looked at couldn't possibly support the syntax.

And now I see that you just reject bugs saying that it couldn't possibly be. Without even checking the code.

Not much on the support side I see.

christian · **Posted:** Tue Aug 03, 2004 12:48 pm

So, I should start looking for a problem, which doesn't exist for any other user, just because "your guy" thinks that Hibernate "doesn't do anything" and the API is just fake?

Give me something to work with. This is just wasting time.

subpaul · **Joined:** Thu Jul 22, 2004 2:15 pm **Posts:** 35

hey smith,

I would check if you are using a good driver for this.. it seems like you are.. because earlier you said it does return 500, but they are not what you want..

So it seems like 2.1.4 is not broken.. I use order by all the time and it works great for me.

You might want to take a look here: http://www.hibernate.org/hib_docs/refer ... l-ordering

Also, at the end of the documentation, they have bunch of queries you can look at to see what you need to do.

I think hibernate is pretty well supported, esp if Christian is here answering questions. Plus their documentation is really good.

HTH,
Pritpal Dhaliwal

nweddle · **Posted:** Tue Aug 03, 2004 1:10 pm

The following query returns the latest 500 rows:

Code:

SELECT * FROM (SELECT * FROM ul_common_log_event WHERE application_name = 'Configuration' ORDER BY cle_id DESC) WHERE ROWNUM <= 500

This HQL query...

Code:

from CommonLogEvent cle where cle.componentIdentification.applicationName = 'Configuration' order by cle.id

with Query q = session.createQuery(query).setMaxResults(500);

produces this Oracle SQL, and returns no records... I'm not sure why two rowum statements are being created.

Code:

select * from ( select row_.*, rownum rownum_ from ( select commonloge0_.cle_id as cle_id, commonloge0_.observed_time as observed2_, commonloge0_.version as version, commonloge0_.situation as situation, commonloge0_.message as message, commonloge0_.status as status, commonloge0_.severity as severity, commonloge0_.log_level as log_level, commonloge0_.address_type as address_9_, commonloge0_.address_name as address10_, commonloge0_.component_name as compone11_, commonloge0_.application_name as applica12_, commonloge0_.instance_name as instanc13_, commonloge0_.thread_name as thread_14_, commonloge0_.environment_name as environ15_ from UL_COMMON_LOG_EVENT commonloge0_ where (commonloge0_.application_name='Configuration' ) order by  commonloge0_.cle_id ) row_ where rownum <= ?) where rownum_ > ?

Unfortunately, adding the order by to my HQL does not do the trick. BTW I am using Hiberante 2.1.1. I know that Hibernate supports sub-selects, so I am hoping there is a way to reproduce my Oracle TOP-N query

gsmith53 · **Joined:** Tue Aug 03, 2004 12:18 pm **Posts:** 11

subpaul,

All I asked was does 2.1.4 support the setMaxResults() syntax. We are having trouble when the table size is large. It has nothing to do with the order by. That works fine. We're just having problems limiting the size of the returned result set. We also can't see anything in the query that actually goes to the database that would limit the number of rows in the resultset.

That leads me to beleive that the database is returning all the rows, and that hibernate is somehow limiting the population of the returned list(). The problem is that we can't find the place in the hibernate code that does this limiting.

Now, my guy tells me that restarting the server several times, and it's now working... who knows why these things don't work... :-). I would still like to know where in the hibernate code this is handled, since it doesn't seem to be handled in the SQL query itself.

I'm not complaining about the product. I'm just saying that all I asked was a simple question. I got a cryptic answer.

christian · **Posted:** Tue Aug 03, 2004 1:14 pm

There have been fixes to the OracleDialect (I assume you use Oracle8) recently, try with 2.1.4 and the current CVS (or wait until I'll release 2.1.5 today).