Sorting: OrderBy and Sort

1. Sorting as a repository contract

If you have ever seen a product list that “jumps” between application restarts, congratulations, you have encountered a lack of sorting. A database is not required to return rows in a “pretty” order. It is required to return the correct data set, and without ORDER BY the order is most often just “whatever happened today.” That is why sorting in backend code is not about aesthetics, but part of the read contract: we must explicitly say which order we consider correct for this use case.

So that it does not feel like “a teacher being picky,” imagine a coffee shop line. If the barista says, “I will serve everyone who is in the room,” but does not say “in order,” things get very creative: whoever is louder gets the latte faster. A query without ORDER BY is roughly that kind of line, except the “loud ones” are the query planner, the current execution plan, and the state of the indexes.

In SQL this looks simple: sorting lives in ORDER BY, and until you write it (or until Spring Data generates it for you), the order is not guaranteed.

2. Two approaches: OrderBy... and Sort

When we create a derived query, we get two convenient ways to tell Spring Data: “please return the data in this order.” Both approaches are good; they just solve different problems. One is “bake the order into the contract,” and the other is “let the calling code choose the sort at runtime.”

First, a small decision map (without trying to turn the lecture into a reference manual):

Next, we will break down both approaches in plain language and in the code of our mini-shop.

3. Fixed sorting: findByStatusOrderByCreatedAtDesc(...)

Fixed sorting is when you already know that the scenario always reads data in one order. For example, “show active products — newest first.” In that case, it is convenient to express the sorting directly in the method name, and then any developer (including you two weeks later) can see it without searching through services and configs.

The syntax for derived queries is very literal: you add OrderBy, then the field name, then the direction Asc or Desc. Here is an example for our catalog — “new active products”:

import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Sorting is "baked" into the method contract: whoever calls it, the order is always the same
    // createdAt is an entity field, not a column name. Under the hood, Spring Data will add ORDER BY ... DESC.
    List<Product> findByStatusOrderByCreatedAtDesc(ProductStatus status);
}

Here createdAt is an entity field, not the created_at column. Spring Data will build a query where ORDER BY createdAt DESC appears (at the SQL level this becomes order by created_at desc, but we always keep “entity ↔ table” in mind).

Why is this convenient as a contract? Because the method name makes it hard to use incorrectly. You call findByStatusOrderByCreatedAtDesc(ACTIVE) and get active products strictly in that order. No extra arguments, no “so how do I sort this?” This kind of method is a good fit for the pieces of code that are “anchor points” for the project.

But there is a downside too: if in a few days you want “active products, but by price,” this method will no longer help. And that is normal: fixed sorting is like a fixed bus route. It is great as long as you need that exact line, and annoying if you suddenly want to go to the next district.

4. Dynamic sorting with Sort

Now let us move to the scenario that appears in real life more often: “give me products by filter, but I want to choose the sorting.” Even if we do not have a web layer right now, the service layer can still have different business methods: in one place we sort by price, in another by name, in another by newest first. And rewriting a separate repository method for each order is the road to a small method monster.

For this, Spring Data offers the Sort parameter. You add it to the derived query signature, and it becomes an ORDER BY on the SQL side.

A minimal repository example:

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // The calling code chooses the sort: Sort will be converted into ORDER BY at the SQL level
    // Important: sorting uses entity properties, not table column names.
    List<Product> findByStatus(ProductStatus status, Sort sort);
}

So we have one method, and the calling code chooses the order.

4.1. Building Sort in code

Sort is built from entity property names. This is an important idea: we sort by "price", "createdAt", "name", "id", not by "price_amount" and not by "created_at".

Example: “active products, expensive ones first.” We will add a second sort field by id so that the order is more stable (more on that below).

import org.springframework.data.domain.Sort;

// "price" and "id" are entity field names, not database columns
Sort sort = Sort.by("price").descending()
        // The second sort key makes the order stable when price values are equal
        .and(Sort.by("id").ascending());

List<Product> products =
        // The repository receives a ready-made sorting rule
        productRepository.findByStatus(ProductStatus.ACTIVE, sort);

The idea here is simple: first sort by descending price, and if the prices are the same, by id in ascending order. This is not a “gimmick for nitpickers,” but a practical way to avoid a situation where products with the same price appear in a different order every time.

4.2. Where to build Sort

The repository is “data access,” and the service is a “use case.” Sorting is part of the use case. That is why Sort is usually built at the service level.

For example, in CatalogService (which we started earlier), you can define the method “find active products by price” like this:

import org.springframework.data.domain.Sort;

import java.util.List;

public List<Product> findActiveProductsByPriceDesc() {
    // The service defines the business meaning: which order is "correct" for this use case
    Sort sort = Sort.by("price").descending()
            // Add a second key so the result does not "float" when prices are equal
            .and(Sort.by("id"));

    // The repository stays universal: it accepts a filter + sorting rule
    return productRepository.findByStatus(ProductStatus.ACTIVE, sort);
}

Yes, this looks a bit “wordy” (it is only sorting!), but in return you get a clear place where it is written why exactly this order is considered correct. The repository remains universal, and the service remains application-specific.

5. Useful nuances of sorting

5.1. Sorting in memory after the repository

Beginners often think: “Well, I can get the list and then sort it with products.sort(...).” And technically, you really can. The problem is that you pay for it in three unpleasant ways: you read more from the database than necessary; you put extra load on the JVM; and you gradually train the project into the habit of “let’s pull everything and figure it out later.”

Here is a typical “how not to do it” example:

import java.util.Comparator;
import java.util.List;

List<Product> products = productRepository.findByStatus(ProductStatus.ACTIVE);

// Sorting in memory: this can be expensive in CPU and memory for large data sets
// Also, you loaded "everything" in advance, although the database could have sorted and returned it more efficiently
products.sort(Comparator.comparing(Product::getPrice).reversed());

On a small data set, this looks harmless. On a real database (where there may be many active products), this leads to pulling hundreds or thousands of rows just to sort them in memory afterwards. A database can sort more efficiently, and that is exactly why ORDER BY exists.

In addition, in-memory sorting does not play well with the future growth of the project: as soon as read-size limits appear (and they almost always do), you will need to sort “on the database side” anyway, which means you will eventually move to Sort/OrderBy.

5.2. Stable order: a second sort key

Stable order means that two identical queries return the data in the same order. This matters not only for “beauty,” but also for predictability. If your main sort key is not unique (for example, products often have the same price or the same creation date), then the order inside that group can “float.”

The simplest and most practical trick is to add a second sort key: usually id. It is almost always unique and almost always present in every entity.

It looks like this:

import org.springframework.data.domain.Sort;

// First key: "newest on top"
Sort sort = Sort.by("createdAt").descending()
        // Second key: fix the order inside equal createdAt values
        .and(Sort.by("id").descending());

List<Product> products =
        // Both keys are converted into ORDER BY created_at DESC, id DESC
        productRepository.findByStatus(ProductStatus.ACTIVE, sort);

Here I intentionally sort id in descending order too: when products are created “at the same moment” (for the database this may be the same second), later inserts usually have a larger id. But that is not a dogma — you choose the direction so that it matches your business meaning.

5.3. What this looks like in SQL

To completely remove the idea that “Sort is a Java object and there is magic somewhere,” it is useful to imagine what happens under the hood. Schematically, it looks like this:

flowchart TD
    S[CatalogService] --> R[ProductRepository method]
    R --> D[Spring Data JPA]
    D --> Q[SQL with ORDER BY]
    Q --> P["PostgreSQL"]
    P --> D
    D --> R
    R --> S

Sort turns into ORDER BY roughly like this (a conceptual example for status = ACTIVE and sorting by price descending):

-- The database applies filtering and sorting: order is part of the result, not "luck"
select p.*
from product p
where p.status = ?
-- Two sort keys: first by price, then by id for stability
order by p.price desc, p.id asc

The important thing is that result ordering is the database's responsibility, not a coincidence of Java code. That is why sorting is best defined at the same level where you define the filter: in the query. Spring Data gives you two convenient control panels (OrderBy... and Sort), but the goal is always the same — a correct ORDER BY.

6. Typical mistakes when sorting with OrderBy and Sort

Mistake #1: relying on the “natural order” of the result without ORDER BY.
Sometimes it seems like “well, it usually comes back by id.” That feeling can last for weeks... until the query execution plan changes one day, an index appears, or you restart the database container. Without sorting, you are not buying an order contract, you are buying a lottery ticket. Sometimes you win, but more often you simply do not notice that you have already lost.

Mistake #2: sorting in memory after loading the data.
On a training database this works. On a real one, it works first, then starts to slow down, then starts to eat memory, and then you suddenly realize that you are reading giant lists into the JVM only to sort them with a comparator. Sorting should happen where the data is — in the database.

Mistake #3: passing a column name to Sort.by(...) instead of an entity field name.
Sort.by("created_at") looks logical if you think in SQL terms. But Spring Data sorts by entity properties. You need Sort.by("createdAt"). This is a typical ORM confusion: you are already living in the world of entities, but your head is still speaking SQL.

Mistake #4: not adding a second sort key for a non-unique field.
If you sort only by price, then products with the same price can end up in “arbitrary order.” It looks like a shaky UI: the user opened the list — one order, refreshed — another. Adding a second key (often id) makes the order stable and reduces the number of strange questions like “why are my products jumping around?”

Mistake #5: building Sort from arbitrary strings without control.
Sort.by(userInput) seems like a quick solution until userInput becomes "dropDatabase" (a joke) or just "prcie" (a typo, no longer a joke). Then you will get a runtime error, and it will look like “everything broke.” Even if you are not writing a web layer yet, it is better to build the habit now: sorting should be constructed from a limited set of allowed options, not from “any string in the world.”