I needed to select an object-id of a row where a value reached a max value. Kinda confusing, so example:
SELECT id, MAX(value) FROM table
GROUP BY id
simple… and now i needed to check, if the value is bigger then something… lets say 5:
SELECT id FROM (
SELECT id, MAX(value) AS bigValue FROM table
GROUP BY id)
WHERE bigValue > 5
and yes. A join would be fine too… but actually i needed this query in JPA. and JPQL can’t handle nested selects… so a different solution was needed: HAVING! having is a select on a group…
SELECT id FROM table
GROUP BY id
HAVING MAX(value) > 5
cool 🙂 JPA didnt handle the HAVING… 🙁