Search code examples
jpaderby

Derby jpa select MAX()

I have a little problem with my query. Here my table table And I want this rows rows

But I only get one row rows

Here my query : 

        Query q = em.createQuery("SELECT OBJECT(historic) FROM Historic historic WHERE historic.user.id = :user_id AND historic.enchere.id = historic.enchere.id AND historic.limcoins = (SELECT MAX(historic.limcoins) FROM Historic historic WHERE historic.enchere.id = historic.enchere.id AND historic.state LIKE :state)");
        q.setParameter("user_id", user_id);
        q.setParameter("state", "none");
        List<Historic> list = q.getResultList();
Solution

  • Okay I found.

    Replace the = oprator by IN

    historic.limcoins IN (SELECT MAX(historic.limcoins) FROM Historic historic WHERE historic.enchere.id = historic.enchere.id AND historic.state LIKE :state)