HQL to return the index of an object in an ordered query result?

Can I use HQL to get the index of an ordered query result?

The only way I know to do this is to get back all of the results from the database, then iterate through all of the results.

For example, given:

<hibernate-mapping>
<class name="Dog" table="DOG">

    <id name="id" type="int" column="DOG_id">
        <meta attribute="scope-set">protected</meta>
        <generator class="native"/>
    </id>

    <property name="age" type="int" />

    <property name="name" type="string" />
</class>    
</hibernate-mapping>

then these types of queries:

//determine where dog with id 43 is in the current search results for dogs ordered by age
Query dogQuery = sess.createQuery("select d.id from Dog as d order by d.age");
List<Dog> dogList = dogQuery.list();
int idx = dogList.indexOf( 43 );

or, a more refined search through the dog list:

Query dogQuery = sess.createQuery("select d.id from Dog as d where (d.name='fluffy' OR d.age >= 3) order by d.age");
List<Dog> dogList = dogQuery.list();
int idx = dogList.indexOf( 43 );

The drawback here is that we are loading every dog.id into memory.

Scenario where I would need this:

displaying a specific query result (of thousands) as a dot on a line. The line, and the dot, gets updated every minute or so. This visualization gives "real time" updates on the rank of a search query

Solution

use the List.indexOf() If the list is extra large then make the list "extra-lazy".

But why do you need the index? Maybe you really don't (and thus you will not need to solve this issue)

Revised answer:

You are doing it completely wrong. All you need is :

select count(*) from dog d, dog d1 where d.age <= d1.age and d1.id = :entityId and d1.id <> d.id

Why this works: This query finds every dog that is the same age or younger than the dog in question (d1) but is not d1. (but does not actual return all those dogs - since we don't care about them).

The count tells you the number of dogs "before" d1. Thus you can tell where d1 appears in the complete list of dogs. Since you don't need to know the position of all the dogs in the list, you don't need to retrieve all the dogs.