How to get objects with max value in Hibernate hql?

Question

I'm working on a query using Hibernate and following is the query

List<ArticleP> articleP = session.createQuery("select ap, max(ap.lastPrice.price) from ArticleP ap "
                + "where ap.stock > 0 and ap.article.category.idCategory = :idCategory "
                + "group by ap.article, ap.prov "
                + "order by ap.article.desc ASC")
                .setProperties(category)
                .list();

I get a multi dimensional array containing an object and a max double, but I want to get an array of objects which has a max value, something like this

List<ArticleP> articleP = session.createQuery("from ArticleP ap "
                    + "where ap.stock > 0 and ap.article.category.idCategory = :idCategory and max(ap.lastPrice.price) "
                    + "group by ap.article, ap.prov "
                    + "order by ap.article.desc ASC")
                    .setProperties(category)
                    .list();

I know the last query is not possible and don't know how can I get the desired data.

Thanks in advance.

score 0 · Answer 1 · answered Nov 16 '15 at 07:57

Ypu can define a subquery and use IN like this

List<ArticleP> articleP = session.createQuery("select ap, max(ap.lastPrice.price) from ArticleP ap "
            + "where ap.stock > 0 and ap.article.category.idCategory = :idCategory "
            + "and ap.id IN (select max(ap.lastPrice.price) from ArticleP ap 
            + "              group by ap.article, ap.prov "
            + "              order by ap.article.desc ASC)")
            .setProperties(category)
            .list();from phone p where p.id not in

How to get objects with max value in Hibernate hql?

1 Answers1