Spring CRUD repository: is there findOneByMaxXYZColumn()?

Question

My requirement:

fetch ONE object (e.g RetainInfo ) from table RETAIN_INFO if VERSION column has max value

Does CRUD repository support for an interface method like

findOneByMaxRetVersionAndCountry("DEFAULT") 

Equivalent db2 sql:

select RET_ID, max(ri.RET_VERSION) from RETAIN_INFO ri  where ri. COUNTRY='DEFAULT'  group by RET_ID  fetch first 1 rows only; 

• This query selects an ID, but I would actually want the RetainInfo object corresponding the SINGLE row returned by the query.

I prefer to get that without using custom query, i.e using findBy or some other method/interface supported by Spring CRUD.

Answer 1

You could use limiting in combination with sorting (spring data reference:limit query results). Declare a method similar to the following in your CrudRepository interface :

RetainInfo findTopByCountryOrderByRetVersionDesc(String country); 

Answer 2

You can also use findFirst to get the first result. Before getting the result, make sure to use Orderby and then the ascending(Asc) or descending(Desc). As an example if you want to order by version and retrieve based on productName

RetainInfo findFirstByProductNameOrderByVersionDesc(String productName);