Calculating rank on an aggregate column in a Hibernate SQLProjection

Question

I am trying to write a query to calculate a rank column based upon an aggregate column. The query is an SQLProjection as part of a Hibernate Criteria query. Here is what I have tried:

String sqlProjection = 
    "(select count(*) from IPTStatistic stat2 where 
               max(s.powerRestarts) > max({alias}.powerRestarts)) as rank)";

ProjectionList list = Projections.projectionList();

list.add(Projections.sqlProjection(sqlRankQuery, new String[]{"rank"}, new Type[]{new IntegerType()})));
list.add(Property.forName("managedObjectName").group());
list.add(Projections.max("powerRestarts").as("maxRestarts"));

Criteria crit = hibernateSessionHelper.getSessionFactory().getCurrentSession().createCriteria(IPTStatistic.class);
crit.setProjection(projection);

crit.list();

When I use a non-aggregate column in the SQL projection, the subselect works and I get the expected results, it is only once I introduce the max() that the error occurs.

This throws a fairly non-specific org.hibernate.exception.GenericJDBCException with message "Could not execute query".

The log shows:

WARN   logExceptions, SQL Error: -458, SQLState: S1000
ERROR  logExceptions, java.lang.NullPointerException java.lang.NullPointerException

I can't pinpoint the problem in the query myself from the above error messages, can anyone give me some pointers on how to correct my query?

---

UPDATE:

I am now using the following sqlProjection as per axtavt's answer below:

String sqlProjection = "(select count(*) from " +
    "(select name from IPTStatistic s group by s.name " + 
    "    having max(s.powerRestarts) > max({alias}.powerRestarts)) " +
    "as r) as rank"

The SQL generated by Hibernate is:

select (select count(*) from (select iptManagedObjectName from IPTStatistic s group by s.iptManagedObjectName having max(s.powerRestarts) > max(this_.powerRestarts)) as r) as rank, this_.iptManagedObjectName as y1_, from IPTStatistic this_ 

I am now getting the error:

WARN   logExceptions, SQL Error: -5581, SQLState: 42581
ERROR  logExceptions, unexpected token: SELECT

If I remove max({alias}.powerRestarts) and replace it with either a constant or max(s.powerRestarts), then the query works (but obviously does not calculate the rank correctly).

---

There seems to be a problem using the {alias} in this sqlProjection query - possibly something to do with the nested subqueries - can anyone help?

Thankyou.

Answer 1

HQL doesn't support subqueries in select list, thus you have two options:

• Write this query in SQL and execute it as a native query

• Write something like

  select max(stat.powerRestarts), stat.managedObjectName 
  from IPTStatistic stat 
  group by stat.managedObjectName
  order by max(stat.powerRestarts) desc
  

  then rank can be deduced programmatically from a row number

UPDATE:

An important point here is that you need to perform two aggregations (max and count) in order to calculate a rank, so that you need two queries to do it:

String sqlProjection = 
    "(select count(*) from " +
    "(select name from IPTStatistic s group by s.name " + 
    "    having max(s.powerRestarts) > max({alias}.powerRestarts)) " +
    "as r) as rank";

Also note the use of having instead of where, since condition should be applied after the first aggregation.