# Lesson 8.4: Sorting and pagination

## [](#_sorting)Sorting

The results of any query can be sorted by adding the `sort` modifier to the query. The following query retrieves the list of every book’s title and page count, and sorts by page count in ascending order, and then takes the shortest 5 books with a limit:

```typeql
match $book isa book, has page-count $count;
sort $count;
limit 5;
fetch { "title": $book.title, "page-count": $book.page-count };
```

```json
{
  "title": "The Iron Giant",
  "page-count": 79
}
{
  "title": "The Motorcycle Diaries: A Journey Around South America",
  "page-count": 160
}
{
  "title": "Physical Principles of Electron Microscopy: An Introduction to TEM, SEM, and AEM",
  "page-count": 196
}
{
  "page-count": 199,
  "title": "Interpretation of Electron Diffraction Patterns"
}
{
  "page-count": 215,
  "title": "The Hitchhiker's Guide to the Galaxy"
}
```

The `sort` modifier takes a variable as an argument, denoting the variable to sort on. The results are sorted in ascending order. In order to sort in descending order, we can add append the keyword `desc` to the modifier as follows to get the 5 longest books:

```typeql
match $book isa book, has page-count $count;
sort $count desc;
limit 5;
fetch { "title": $book.title, "page-count": $book.page-count };
```

```json
{
  "title": "The Complete Calvin and Hobbes",
  "page-count": 1451
}
{
  "title": "Classical Mythology",
  "page-count": 820
}
{
  "title": "The Odyssey",
  "page-count": 656
}
{
  "title": "Dune",
  "page-count": 624
}
{
  "title": "One Hundred Years of Solitude",
  "page-count": 458
}
```

Though sorting is always in ascending order by default, we can specify this explicitly using the keyword `asc` in the same way as `desc`.

The ordering of strings is determined according [lexicographic ordering](https://en.wikipedia.org/wiki/Lexicographic_order).

## [](#_pagination)Pagination

We can do simple pagination the sorted results of any query by combining the `limit` and `offset` modifiers. In the following example, we add a `limit` modifier to the previous query to retrieve 5 to 10th books with the lowest page counts.

```typeql
match $book isa book, has page-count $count;
sort $count;
offset 5;
limit 5;
fetch { "title": $book.title, "page-count": $book.page-count };
```

```json
{
  "page-count": 240,
  "title": "The Mummies of Urumchi"
}
{
  "page-count": 260,
  "title": "Business Secrets of The Pharoahs"
}
{
  "title": "To Kill a Mockingbird",
  "page-count": 281
}
{
  "title": "Pride and Prejudice",
  "page-count": 295
}
{
  "title": "Under the Black Flag: The Romance and the Reality of Life Among the Pirates",
  "page-count": 296
}
```

The `sort`, `limit`, and `offset` modifiers can all be used individually or in any combination. They apply in the order they are written!

_Note: offset/limit pagination is easy, but it is not efficient as the offset computes, then discards values!_ More efficient pagination uses a known, ordered attribute value and fetches pages at a time by restricting the search to answers with value greater than the last value: `match <pattern>; $attribute > [last-returned-value]`.

Exercise

Write a Fetch query to retrieve the titles of the five most recently published books in the database.

You may find it useful to refer to the bookstore’s schema.

Schema

```typeql
define

entity book @abstract,
    owns isbn @card(0..2),
    owns isbn-13 @key,
    owns isbn-10 @unique,
    owns title,
    owns page-count,
    owns genre @card(0..),
    owns price,
    plays contribution:work,
    plays publishing:published,
    plays promotion-inclusion:item,
    plays order-line:item,
    plays rating:rated,
    plays recommendation:recommended;

entity hardback sub book,
    owns stock;

entity paperback sub book,
    owns stock;

entity ebook sub book;

entity contributor,
    owns name,
    plays contribution:contributor,
    plays authoring:author,
    plays editing:editor,
    plays illustrating:illustrator;

entity company @abstract,
    owns name;

entity publisher sub company,
    plays publishing:publisher;

entity courier sub company,
    plays delivery:deliverer;

entity publication,
    owns year,
    plays publishing:publication,
    plays locating:located;

entity user,
    owns id @key,
    owns name,
    owns birth-date,
    plays action-execution:executor,
    plays locating:located,
    plays recommendation:recipient;

entity order,
    owns id @key,
    owns status,
    plays order-line:order,
    plays action-execution:action,
    plays delivery:delivered;

entity promotion,
    owns code @key,
    owns name,
    owns start-timestamp,
    owns end-timestamp,
    plays promotion-inclusion:promotion;

entity review,
    owns id @key,
    owns score,
    owns verified,
    plays rating:review,
    plays action-execution:action;

entity login,
    owns success,
    plays action-execution:action;

entity address,
    owns street,
    plays delivery:destination,
    plays locating:located;

entity place @abstract,
    owns name,
    plays locating:located,
    plays locating:location;

entity city sub place;

entity state sub place;

entity country sub place;

relation contribution,
    relates contributor,
    relates work;

relation authoring sub contribution,
    relates author as contributor;

relation editing sub contribution,
    relates editor as contributor;

relation illustrating sub contribution,
    relates illustrator as contributor;

relation publishing,
    relates publisher,
    relates published,
    relates publication;

relation promotion-inclusion,
    relates promotion,
    relates item,
    owns discount;

relation order-line,
    relates order,
    relates item,
    owns quantity,
    owns price;

relation rating,
    relates review,
    relates rated;

relation action-execution,
    relates action,
    relates executor,
    owns timestamp;

relation delivery,
    relates deliverer,
    relates delivered,
    relates destination;

relation locating,
    relates located,
    relates location;

relation recommendation,
    relates recommended,
    relates recipient;

attribute isbn @abstract, value string;
attribute isbn-13 sub isbn;
attribute isbn-10 sub isbn;
attribute title, value string;
attribute page-count, value integer;
attribute genre, value string;
attribute stock, value integer;
attribute price, value double;
attribute discount, value double;
attribute id, value string;
attribute code, value string;
attribute name, value string;
attribute birth-date, value datetime;
attribute street, value string;
attribute year, value integer;
attribute quantity, value integer;
attribute score, value integer;
attribute verified, value boolean;
attribute timestamp, value datetime;
attribute start-timestamp, value datetime;
attribute end-timestamp, value datetime;
attribute status, value string @regex("^(paid|dispatched|delivered|returned|canceled)$");
attribute success, value boolean;

# TODO: Change to check
fun is_review_verified_by_purchase($review: review) -> { order }:
  match
    ($review, $product) isa rating;
    ($order, $product) isa order-line;
    ($user, $review) isa action-execution, has timestamp $review-time;
    ($user, $order) isa action-execution, has timestamp $order-time;
    $review-time > $order-time;
  return { $order };

fun book_recommendations_for($user: user) -> {book}:
  match
    $new-book isa book;
    {
        let $new-book in book_recommendations_by_author($user);
    } or {
        let $new-book in book_recommendations_by_genre($user);
    };
  return { $new-book };

fun book_recommendations_by_genre($user: user) -> { book }:
match
    $user isa user;
    $liked-book isa book;
    {
        ($user, $order-for-liked) isa action-execution;
        ($order-for-liked, $liked-book) isa order-line;
    } or {
        ($user, $review-for-liked) isa action-execution;
        ($review-for-liked, $liked-book) isa rating;
        $review-for-liked has score >= 7;
    };
    $new-book isa book;
    not { {
        ($user, $order-for-new) isa action-execution;
        ($order-for-new, $new-book) isa order-line;
    } or {
        ($user, $review-for-new) isa action-execution;
        ($review-for-new, $new-book) isa rating;
    }; };
    $liked-book has genre $shared-genre;
    $new-book has genre $shared-genre;
    not { {
        $shared-genre == "fiction";
    } or {
        $shared-genre == "nonfiction";
    }; };
  return { $new-book };

fun book_recommendations_by_author($user: user) -> { book }:
  match
    $user isa user;
    $liked-book isa book;
    {
        ($user, $order-for-liked) isa action-execution;
        ($order-for-liked, $liked-book) isa order-line;
    } or {
        ($user, $review-for-liked) isa action-execution;
        ($review-for-liked, $liked-book) isa rating;
        $review-for-liked has score >= 7;
    };
    $new-book isa book;
    not { {
        ($user, $order-for-new) isa action-execution;
        ($order-for-new, $new-book) isa order-line;
    } or {
        ($user, $review-for-new) isa action-execution;
        ($review-for-new, $new-book) isa rating;
    }; };
    ($liked-book, $shared-author) isa authoring;
    ($new-book, $shared-author) isa authoring;
  return { $new-book };

fun order_line_best_price($line: order-line) -> { double }:
  match
    ($order) isa action-execution, has timestamp $order-time;
    $line isa order-line, links ($order, $item);
    $item has price $retail-price;
    let $time_value = $order-time;
    let $best-discount = best_discount_for_item($item, $time_value);
    let $discounted-price = round(100 * $retail-price * (1 - $best-discount)) / 100;
    $line has quantity $quantity;
    let $line-total = $quantity * $discounted-price;
  return { $line-total };

fun best_discount_for_item($item: book, $order-time: datetime) -> double:
  match
    {
        $inclusion isa promotion-inclusion,
            links ($promotion, $item),
            has discount $discount-attr;
        $promotion has start-timestamp <= $order-time,
            has end-timestamp >= $order-time;
        let $discount = $discount-attr;
    } or {
        let $discount = 0.0; # default
    };
return max($discount);

fun transitive_places($place: place) -> { place }:
  match
    {
      locating (located: $place, location: $parent);
    } or {
      locating (located: $place, location: $middle);
      let $parent in transitive_places($middle);
    };
  return { $parent };
```

Sample solution

```typeql
match
$book isa book;
$publication isa publication, has year $year;
publishing ($book, $publication);
sort $year desc;
limit 5;
fetch { "title": $book.title };
```

[Aggregations](../8.2-aggregations/index.md) [Subqueries](../8.4-subqueries/index.md)

[Edit on GitHub](https://github.com/typedb/typedb-docs/edit/3.x-development/academy/modules/ROOT/pages/8-composing-clauses/8.3-sorting-and-pagination.adoc) Edit this page on GitHub.