Min and Max from same subquery

Question

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?

Does this answer your question? [UNION to JPA Query](https://stackoverflow.com/questions/18958614/union-to-jpa-query) — Queeg, Sep 26 '22 at 15:30
@HiranChaudhuri No. Union will create two result-rows. I need one result-row. — Grim, Sep 26 '22 at 15:37

Karol Dowbecki · Answer 1 · 2022-09-26T15:35:53.523

0

If you have a huge database this problem probably should be addressed by tweaking the schema design. Most RDBMS would have to do a full table scan on Payment table to find both minimum and maximum amount value. You can check this by looking at EXPLAIN output for your current query.

Since you are concerned with the query speed you can denormalize the schema and store the maxAmount and minAmount columns in Person table or create a new PersonMinMaxPayment table. You can then update these when new row is inserted into Payment table e.g. by declaring @PrePersist or creating a trigger.

There are also few more possibilities if your system can be eventually consistent. You can recalculate these values with a delay e.g. as a once a day batch job.

edited Sep 26 '22 at 15:35

answered Sep 26 '22 at 15:29

Karol Dowbecki

43,645
9
78
111

Sorry, plain JPQL only. – Grim Sep 26 '22 at 15:35
@Grim Let me know if you figure it out! – Karol Dowbecki Sep 26 '22 at 15:36
I think about a view, mapped as a readonly-class. – Grim Sep 26 '22 at 15:38
1

@Grim view is not a a JPQL solution – Karol Dowbecki Sep 26 '22 at 15:38
Yea, thats why I can not used it either. :) – Grim Sep 26 '22 at 15:39
Another approach is `concat(min(x),' to ',max(x))` in jpql. but this will generate one field instead of two. – Grim Sep 26 '22 at 15:44
@Grim you might want to check if the performance of the query would be any different with `concat()` approach. My guess no, but I don't know your database. – Karol Dowbecki Sep 26 '22 at 15:46
1

Postgres, its much faster using concat. – Grim Sep 26 '22 at 15:48
@Grim Nice. Any chance you can show the `EXPLAIN ANALYZE` output for the first query and then for the second query? – Karol Dowbecki Sep 26 '22 at 19:33

score 0 · Accepted Answer · answered Sep 27 '22 at 09:09

0

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 Sep 27 '22 at 09:09

Grim

1,938
10
56
123

Min and Max from same subquery

2 Answers2