Hibernate version:1.02

Name and version of the database you are using: SQL 2000

Hello All,

Is it possible to transfer this sql to HQL?

select count(*) from (select top 100 * from js_point order by createddate) as point

Some property of my POCO:

Point.ID
Point.Name
Point.CreatedDate

Thank you

I must be missing something - wouldn't that always return 100?

Dear K-dub,

there is possible that records are less than 100.

Get the count back, set to 100 if it is over 100.

that's my HQL

select count(pt) from ( select ptt from Point ptt order by ptt.CreatedDate desc)

I get an exception

Exception Details: NHibernate.QueryException: in expected: select [select count(pt) from ( select ptt from JasonSoft.Model.Point ptt order by ptt.CreatedDate desc)]


How to write the HQL like the SQL above?
thank you

Why don't you just do "select count(*) from Point pt"? Ordering doesn't change the count anyway. Or are you going to expand this query further?

Sure...

Firstly, the point table store information about one place's city, longitude、 latitude、description、createddate、lastupdated..ect..

I am going to create a web application that integrate GoogleMap.

There are two features of the application.

Get the newest 100 points that were created by users.
Get the lastest 100 points that were modified by users.

moreover, the result can be filtered by city again.

I am planning to display the data with DataGrid.
Also, I am using custom pager (that's problem...I need to do the pager by myself, not Datagrid. The reason I do that because of performance. I don't want datagrid to bind all the records. I just want the grid to display the records depend on what the page is...)

Therefore, I need to give count of total records to datagrid.

In SQL way, I do this way

select count(*) from (select top 100 * from js_point order by createddate) as point

select count(*) from (select top 100 * from js_point order by lastupdated) as point

I have tried something as following...however, no lucky...

select count(*) from js_point order by createddate
(or with group by...)


Hope that's useful...thank you

I found that "select count(m) from MyObject m" doesn't work, but if you do it based on the id of the object it'll be fine - "select count(m.id) from MyObject m"

Not sure if this will help, but it's worth a shot.

Symon.

Yes, you are right...

but the problem is order by...
that's the reason I'm using subquery...

select count(*) from (select top 100 * from js_point order by lastupdated) as point

I am going to do this in SQL way, and give up HQL
thank you

I too have the same problem. When using Hibernates paging functionality I sometimes need to show the total amount of hits (as if the query wasn't paged). Some of theese HQLs dosn't translate to any working count-query.

Is there a way to make the thread starters query work? I.e. count the result of any given generic HQL query.