[FIXED] Min and Max from same subquery

Issue

If a query having a subselect for the highest money payed and the lowest money payed in example:

SELECT p.firstname, 
       p.lastname, 
       (SELECT MAX(pmt.amount) 
          FROM Payment pmt 
         WHERE pmt.person.id = p.id)  maxAmount,
       (SELECT MIN(pmt.amount) 
          FROM Payment pmt 
         WHERE pmt.person.id = p.id)  minAmount,
  FROM Person p

We need to write two subqueries. This have a bad performance on huge databases.

Any solutions in plain JPQL?

Solution

I used this:

   SELECT p.firstname,
          p.lastname, 
          MAX(pmt.amount),
          MIN(pmt.amount)
     FROM Person p
LEFT JOIN p.payments pmt
    GROUP BY p.firstname, p.lastname

Answered By - Grim
Answer Checked By - Gilberto Lyons (JavaFixing Admin)