skip to content

How do Distinct and the First/Top limiting keywords behave in derived query methods?

level: middleimportance: should knowfreq 58%

answer

  1. Distinct = SELECT DISTINCT
  2. First == Top synonyms; Top3/First5
  3. real SQL LIMIT not in-memory
  4. pair with OrderBy for determinism
  5. no Last keyword

basics

~10 s

Distinct adds SELECT DISTINCT to remove duplicate rows. First and Top limit how many rows come back: findFirstByOrderByScoreDesc returns one row; findTop10By... returns ten. First and Top are interchangeable.

solid answer

~50 s

`Distinct` inserts into the subject to produce `SELECT DISTINCT`, deduplicating rows — useful after a join that fans out. `First` and `Top` are synonyms that cap the result set: `findFirstBy...` / `findTopBy...` limit to one, and you can attach a number — `findTop3By...`, `findFirst5By...` — to limit to N. They only make sense with an ORDER BY (via `OrderBy...` in the name or a `Sort`/`Pageable` argument) so 'first' is deterministic. The limit is applied by the database (LIMIT/ROWNUM/fetch-first) — it is a real SQL limit, not in-memory truncation. You can return a single entity, `Optional`, or a `List` from a Top/First method; combining `findFirst...` with a `Pageable` lets the method further page within the capped window. `Distinct` on JPA entities operates at the row level and can interact awkwardly with paging + collection fetch joins.

code

java · 14 lines
java
public interface ScoreRepository extends JpaRepository<Score, Long> {

    // SELECT DISTINCT ... (dedupe after a fan-out join)
    List<Score> findDistinctByGameName(String game);

    // LIMIT 1, deterministic via OrderBy
    Optional<Score> findFirstByPlayerOrderByValueDesc(String player);

    // LIMIT 3 with a dynamic Sort argument
    List<Score> findTop3ByGameName(String game, Sort sort);

    // Distinct + Top + sort combined
    List<Score> findDistinctTop5ByOrderByValueDesc();
}

go deeper

for a junior

Know Distinct dedupes and First/Top limit the number of rows.

for a middle

State First==Top, the Top<N> numeric form, DB-side limiting, and the need for an ORDER BY.

for a senior

Explain Distinct's fan-out use case and its interaction with collection fetch joins + pagination.

for a principal

Discuss why Top gives limit-only (no offset), pushing offset paging to Pageable/@Query, and Hibernate-6 DISTINCT semantics vs the legacy in-memory pagination pitfall.

## Distinct Placed in the subject — `findDistinctByLastName`, `findPeopleDistinctByLastName` — it emits `SELECT DISTINCT`. Its main use is collapsing duplicate root rows produced by a **JOIN that fans out** (e.g. joining a to-many). Caveats: - `DISTINCT` in JPQL/SQL dedupes on the **selected columns/entity identity**, which is usually the root entity's row; two distinct entities are never merged. - With **fetch joins to collections + pagination**, `DISTINCT` historically forced Hibernate to fetch everything and paginate in memory (the classic `HHH000104` warning). Hibernate 6 handles entity-query DISTINCT more gracefully, but it is still a place to be careful. ## First and Top — limiting `Top` and `First` are **exact synonyms**; pick one for readability. - `findFirstBy...` / `findTopBy...` → limit **1**. - `findTop10By...` / `findFirst5By...` → limit **N** (number goes right after the keyword). - The limit is pushed to SQL as the dialect's LIMIT clause (`LIMIT n`, `FETCH FIRST n ROWS ONLY`, Oracle `ROWNUM`, etc.). It is **not** post-fetch truncation in Java. ### Ordering matters 'First' / 'Top' without an ORDER is non-deterministic — the DB returns *some* rows. Always pair with either a name-based `OrderBy...` or a dynamic `Sort`/`Pageable` parameter: ``` User findFirstByActiveTrueOrderBySignupDateAsc(); List<User> findTop3ByStatus(Status s, Sort sort); ``` ### Return types `findFirst.../findTop1...` can return `T`, `Optional<T>`, or `List<T>` (size 0-1). `findTopN...` returns a `List`. You may also combine a Top method with `Pageable` — the Top acts as an outer cap and the `Pageable` pages *within* that window. ## Combining `findDistinctTop5ByOrderByScoreDesc` is legal — distinct + top5 + sort together. ## When to use - `First/Top` for 'the latest', 'the highest', 'newest N' style lookups without a full `Pageable`. - `Distinct` only when a join genuinely duplicates roots — otherwise it is wasted DB work. ## Gotchas - No `Last` keyword — invert the sort direction instead (`OrderBy...Desc`). - `Top`/`First` express only a fixed limit and no offset; if you need offset-based paging you must use `Pageable` (which is what forces `@Query`/`Pageable` for arbitrary limit+offset — see the paging leaf). - Numeric limit is parsed from the name literally; `findTopBy` == `findTop1By`.

  • Is the row limit from findTop10By applied in the database or in Java?
    In the database — Spring generates the dialect's LIMIT/FETCH-FIRST clause, so only up to ten rows are fetched, not fetched-then-truncated.
  • Why should First/Top always be combined with an ORDER BY?
    Without an explicit sort, 'first' is whatever order the DB happens to return, so results are non-deterministic; OrderBy (or a Sort/Pageable arg) makes the limited result well-defined.

saying these in an interview costs you the question

  • Saying Top and First differ in behavior
  • Claiming the limit is applied in memory after loading all rows
  • Believing there is a Last keyword
  • Thinking Distinct can merge two different entities into one

context

resolving links…