MIN / MAX functions in Spring Data JPA with Amazon Aurora DB ( greatest-n-per-group Query)

Question

I have this query spring spring data, I want to know if it is possible in 1 query also the get the updateDate from where the min Price occurs

@Query("select min (hp.price) from HotelPrice hp where hp.hotel = ?1 and hp.updateDate > ?2 ")
float getMinPrice (Hotel hotel, Date date);

Answer 1

SELECT  hp.price, 
        hp.updateDate
    FROM  HotelPrice AS hp
    WHERE  hp.hotel = ?1
      AND  hp.updateDate > ?2 
    ORDER BY hp.price ASC
    LIMIT 1

(I'll leave it up to you to run that in a Spring context.)

This is actually simpler than "greatest-n-per-group", or even "groupwise max". Simply order the output (ORDER BY) and pick off the first row (LIMIT 1).