Fetch the Most Recent Record in Spring Data JPA Using LIMIT 1 Equivalent

In many applications, it is common to retrieve the most recent record for a specific entity—such as the latest transaction, the newest log entry, or the last update associated with a user. In SQL, this is typically achieved using an ORDER BY clause followed by LIMIT 1. However, Spring Data JPA does not support native SQL syntax like LIMIT in derived query method names. Fortunately, it offers a few clean and idiomatic alternatives that integrate well with JPA providers like Hibernate and databases such as PostgreSQL, MySQL, or SQL Server.

In this article, we’ll explore:

• Why LIMIT 1 doesn’t work in Spring Data JPA queries
• Alternatives using method names and Pageable
• A full working solution using PageRequest.of(0, 1)
• Best practices to avoid NonUniqueResultException

💡 Problem Statement

Suppose we have a table securities_positions with a column report_date_time, and we want to get the most recent record for a given account ID:

SELECT TOP 1 * 
FROM dbo.securities_positions 
WHERE securities_account_resource_id = 'some-id' 
ORDER BY report_date_time DESC;

This works well in SQL Server with TOP 1, or LIMIT 1 in MySQL. But how do we translate this to Spring Data JPA?

🚫 Why LIMIT 1 Doesn’t Work in Spring JPA Method Names

Spring Data JPA method naming strategy doesn’t support SQL clauses like LIMIT, TOP, or FETCH FIRST ROW ONLY. If you use:

Optional<SecuritiesPosition> findFirstBySecuritiesAccountResourceIdOrderByReportDateTimeDesc(String id);

…it should return a single result, but in some cases (especially with custom queries or improper mappings), you might encounter:

javax.persistence.NonUniqueResultException: query did not return a unique result: 3

This typically happens when:

• A JPQL query is used without LIMIT
• The return type is not List<T> or Optional<T> and more than one result is returned

✅ The Recommended Solution: Use Pageable with PageRequest.of(0, 1)

You can control the result limit precisely using the Pageable interface. Here’s how to do it:

🔹 Repository Interface

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SecuritiesPositionsRepository extends JpaRepository<SecuritiesPosition, Long> {

    List<SecuritiesPosition> findBySecuritiesAccountResourceIdOrderByReportDateTimeDesc(
        String securitiesAccountResourceId, Pageable pageable);
}

🔹 Default Wrapper Method

To simplify usage and return a single record:

import org.springframework.data.domain.PageRequest;

import java.util.Optional;

default Optional<SecuritiesPosition> findMostRecentPositionByAccount(String accountId) {
    return findBySecuritiesAccountResourceIdOrderByReportDateTimeDesc(accountId, PageRequest.of(0, 1))
           .stream()
           .findFirst();
}

📋 Full Example

🔸 Entity Class

@Entity
@Table(name = "securities_positions")
public class SecuritiesPosition {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "securities_account_resource_id")
    private String securitiesAccountResourceId;

    @Column(name = "report_date_time")
    private LocalDateTime reportDateTime;

    // Getters and Setters
}

🔸 Usage in a Service

@Service
public class PositionService {

    private final SecuritiesPositionsRepository repository;

    public PositionService(SecuritiesPositionsRepository repository) {
        this.repository = repository;
    }

    public Optional<SecuritiesPosition> getLatestPosition(String accountId) {
        return repository.findMostRecentPositionByAccount(accountId);
    }
}

⚠️ Pitfall: Avoid getSingleResult() in Custom Queries

If you try to use @Query with getSingleResult(), ensure the query returns only one row or handle the result via List<T> or Optional<T> to avoid exceptions:

@Query("SELECT sp FROM SecuritiesPosition sp WHERE sp.securitiesAccountResourceId = :accountId ORDER BY sp.reportDateTime DESC")
List<SecuritiesPosition> findLatestByAccountId(@Param("accountId") String accountId, Pageable pageable);

🏁 Conclusion

While Spring Data JPA doesn’t let you write LIMIT 1 directly in query methods, you can achieve the same behavior cleanly and safely using PageRequest with Pageable. This method avoids NonUniqueResultException, respects ordering, and integrates naturally with Spring’s repository abstraction.

✅ Use Pageable to limit rows
✅ Avoid relying on exact row count
✅ Always check for Optional.empty() to avoid NullPointerException