How do you bound a result set in Spring Data using the Top/First keywords versus the dynamic Limit parameter?

Top == First, number optional (default 1)
findTop10ByOrderByScoreDesc()
Limit.of(n) parameter — Spring Data 3.2+
Limit + Sort OK, Limit + Pageable rejected
no OrderBy => arbitrary N rows

basics

~20 s

Static bound: put Top or First plus a number in the method name, e.g. findTop10ByOrderByScoreDesc. Dynamic bound: add a Limit parameter — Limit.of(n) — passed at call time. Both cap how many rows come back.

solid answer

~40 s

There are two ways to cap results without full pagination. Statically, the derived-query keywords Top and First (interchangeable) with an optional number limit the result: findTop10ByOrderByScoreDesc(), findFirstByOrderByCreatedAtDesc() (no number means one). They pair naturally with an OrderBy clause so 'top' is meaningful. Dynamically, Spring Data 3.2+ adds the Limit interface: declare a Limit parameter — List<User> findByActive(boolean active, Limit limit) — and pass Limit.of(20) or Limit.unlimited() at call time, so the cap is decided at runtime. You can combine Limit with a Sort parameter. Crucially you cannot combine Limit with Pageable — Pageable already carries a size, so passing both is rejected. Use Top/First when the bound is fixed and known at design time; use Limit when the caller chooses the count but you don't want the full Page/Slice machinery or a count query.

code

java · 16 lines

java

public interface ScoreRepository extends JpaRepository<Score, Long> {

    // Static bound via Top/First keyword (number optional -> 1)
    List<Score> findTop10ByOrderByPointsDesc();
    Optional<Score> findFirstByPlayerOrderByCreatedAtDesc(String player);

    // Dynamic bound via Limit parameter (Spring Data 3.2+), combined with Sort
    List<Score> findByPlayer(String player, Sort sort, Limit limit);

    // INVALID: Limit and Pageable together is rejected at runtime
    // List<Score> findByPlayer(String player, Pageable pageable, Limit limit);
}

// caller chooses the cap at runtime
List<Score> latest = repo.findByPlayer("neo",
        Sort.by("createdAt").descending(), Limit.of(5));

for a junior

Know that Top/First in the method name caps rows and pairs with OrderBy.

for a middle

Contrast static Top/First with the dynamic Limit parameter and the Limit-vs-Pageable rule.

for a senior

Explain 3.2 availability, Limit + Sort composition, non-determinism without ordering, and choosing Limit over Page to skip count queries.

for a principal

Standardize when the team uses fixed keywords vs dynamic Limit vs full pagination, considering API contracts and query-plan stability.

The goal. Sometimes you don't want a full paginated result — you just want the first N rows, e.g. "top 5 highest scores" or "most recent 10 events". Spring Data offers two mechanisms.

1. Top / First keywords (static, in the method name). In a derived query method, the words Top and First are interchangeable and appear right after find (or read/query/get). An optional number sets the bound:

findTop10ByOrderByScoreDesc() — the 10 highest scores.
findFirst5ByStatus(String status) — first 5 matching rows.
findFirstByOrderByCreatedAtDesc() / findTopByOrderByCreatedAtDesc() — no number means 1.
findDistinctTop3By... — Distinct may combine with the limit.

Because "top" only means something relative to an ordering, you almost always pair it with an OrderBy clause in the method name (or supply a Sort parameter). The bound is fixed in the method name at compile time.

Limiting keywords also work with Pageable/Sort parameters and with a Page/Slice return type — the limit is applied within the restricted result set. But mixing a fixed keyword limit with a Pageable that also pages is unusual; the more common runtime need is the Limit parameter below.

2. The Limit parameter (dynamic, decided at call time). Since Spring Data 3.2 (Spring Boot 3.2) there is org.springframework.data.domain.Limit, an interface you add as a method parameter:

java

List<User> findByActive(boolean active, Limit limit);

Callers pass Limit.of(20) or Limit.unlimited(). This gives you a runtime-chosen cap without encoding the number in the method name and without the Page/Slice return-type ceremony (no count query). Limit.isLimited() / Limit.max() let you inspect it; Limit.unlimited() means no cap.

Combining rules.

Limit can be combined with a Sort parameter — order first, then cap.
Limit cannot be combined with Pageable. A Pageable already defines a page size, so passing both is contradictory and Spring rejects it (throws at runtime). Choose one.
Top/First in the name is compatible with a Sort parameter or OrderBy in the name.

Behavior details.

Under the hood all of these emit the store's row-limiting clause (LIMIT, FETCH FIRST n ROWS ONLY, etc.).
A limited query can return a single aggregate, a List, an Optional, a Stream, or even a Slice/Page — the limit caps the underlying set first.
Requesting findFirstBy... on a query that matches nothing yields an empty Optional/List, not an exception.

Gotchas.

Forgetting the OrderBy makes Top/First return arbitrary rows — the database is free to pick any N.
Passing both Limit and Pageable fails — a frequent mistake when refactoring from paging to a simple cap.
Limit is 3.2+; on older Spring Data you only have the keyword approach or Pageable.

When to use which. Use Top/First when the count is a fixed part of the contract ("the 3 latest"). Use Limit when the caller supplies the count dynamically but you want a plain List without pagination overhead. Use full Pageable when you need offsets/page navigation and totals.

**The goal.** Sometimes you don't want a full paginated result — you just want *the first N rows*, e.g. "top 5 highest scores" or "most recent 10 events". Spring Data offers two mechanisms. **1. Top / First keywords (static, in the method name).** In a *derived query method*, the words `Top` and `First` are **interchangeable** and appear right after `find` (or `read`/`query`/`get`). An optional number sets the bound: - `findTop10ByOrderByScoreDesc()` — the 10 highest scores. - `findFirst5ByStatus(String status)` — first 5 matching rows. - `findFirstByOrderByCreatedAtDesc()` / `findTopByOrderByCreatedAtDesc()` — **no number means 1**. - `findDistinctTop3By...` — `Distinct` may combine with the limit. Because "top" only means something relative to an ordering, you almost always pair it with an `OrderBy` clause in the method name (or supply a `Sort` parameter). The bound is fixed in the method name at compile time. Limiting keywords also work with `Pageable`/`Sort` parameters and with a `Page`/`Slice` return type — the limit is applied within the restricted result set. But mixing a fixed keyword limit with a `Pageable` that also pages is unusual; the more common runtime need is the `Limit` parameter below. **2. The Limit parameter (dynamic, decided at call time).** Since **Spring Data 3.2** (Spring Boot 3.2) there is `org.springframework.data.domain.Limit`, an interface you add as a *method parameter*: ```java List<User> findByActive(boolean active, Limit limit); ``` Callers pass `Limit.of(20)` or `Limit.unlimited()`. This gives you a runtime-chosen cap without encoding the number in the method name and without the `Page`/`Slice` return-type ceremony (no count query). `Limit.isLimited()` / `Limit.max()` let you inspect it; `Limit.unlimited()` means no cap. **Combining rules.** - `Limit` **can** be combined with a `Sort` parameter — order first, then cap. - `Limit` **cannot** be combined with `Pageable`. A `Pageable` already defines a page size, so passing both is contradictory and Spring rejects it (throws at runtime). Choose one. - `Top`/`First` in the name is compatible with a `Sort` parameter or `OrderBy` in the name. **Behavior details.** - Under the hood all of these emit the store's row-limiting clause (`LIMIT`, `FETCH FIRST n ROWS ONLY`, etc.). - A limited query can return a single aggregate, a `List`, an `Optional`, a `Stream`, or even a `Slice`/`Page` — the limit caps the underlying set first. - Requesting `findFirstBy...` on a query that matches nothing yields an empty `Optional`/`List`, not an exception. **Gotchas.** - Forgetting the `OrderBy` makes `Top`/`First` return *arbitrary* rows — the database is free to pick any N. - Passing both `Limit` and `Pageable` fails — a frequent mistake when refactoring from paging to a simple cap. - `Limit` is 3.2+; on older Spring Data you only have the keyword approach or `Pageable`. **When to use which.** Use `Top`/`First` when the count is a fixed part of the contract ("the 3 latest"). Use `Limit` when the caller supplies the count dynamically but you want a plain `List` without pagination overhead. Use full `Pageable` when you need offsets/page navigation and totals.

Can you pass both a Limit and a Pageable to the same repository method?
No. Pageable already encodes a page size, so combining it with Limit is contradictory and Spring Data rejects it at runtime. Use one or the other.
findTop5ByStatus(status) with no OrderBy — which 5 rows come back?
Any 5 the database chooses; without an ordering the result is non-deterministic. Add OrderBy in the name or a Sort parameter to make 'top' meaningful.

saying these in an interview costs you the question

Believing Top and First differ in behavior
Thinking Top/First returns a meaningful 'top' without any ordering
Combining Limit and Pageable in one method signature
Assuming Limit exists in pre-3.2 Spring Data

How do you bound a result set in Spring Data using the Top/First keywords versus the dynamic Limit parameter?

answer

basics

solid answer

code

go deeper

context

How do you bound a result set in Spring Data using the Top/First keywords versus the dynamic Limit parameter?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~2 min

▾common follow-ups2 questions

▾red flags4 flags

§ context

▾related questions3 questions

▾related topics2 topics

answer

go deeper

context