How to achieve first max record in mysql with Spring boot jpa 1.11

Question

I need the max first record in the application, I'm working with Spring boot JPA and MYSQL query I have tried are,

 @Query("select d from Table where d.StatusType=:StatusType and d.tableNumber< :cuurentTableNumber ORDER BY d.tableNumber DESC  LIMIT 1",nativeQuery = true)
    Draw findTop1ByOrderByDrawNumberDesc(@Param("StatusType") Table.StatusType StatusType, @Param("tableNumber")int cuurentTableNumber);


@Query("select d from Table d where d.StatusType=:StatusTypeand d.tableNumber<:cuurentTableNumber")
    Draw findTop1ByOrderByDrawNumberDesc(@Param("StatusType") Table.StatusType StatusType, @Param("tableNumber")int cuurentTableNumber , Pageable pageable);

And other trials too, but nothing is working many times it gave me an error of multiple record found. And I need only one record which is Max but less than current number.

LIMIT does not work with JPA, so cant use that one.

Does anybody has any idea how to achieve this.

Possible duplicate of [How do I write a MAX query with a where clause in JPA 2.0?](https://stackoverflow.com/questions/16348354/how-do-i-write-a-max-query-with-a-where-clause-in-jpa-2-0) — Flown, Jun 29 '17 at 07:54
I'm using JPA 1.11 and I'm looking forward how can I use findFirstByOrderByTableNumberDesc function if its available, and why it is not working for me — tyro, Jun 29 '17 at 08:13

score 0 · Answer 1 · answered Jun 29 '17 at 10:28

I achieved it by doing following,

@Query("select d from Table d where " +
        "d.tableNumber <> :tableNumber " +
        "and (" +
        "(d.statusType = :statusType) " +
        ") ORDER BY d.TableNumber desc")
Iterable<Table> findPreviousNumber(@Param("tableNumber") int tableNumber,
                                @Param("statusType") statusType closeStatusType);

Still will look into it why this is not working with findFirst/Top

How to achieve first max record in mysql with Spring boot jpa 1.11

1 Answers1