# Sorting and Pagination

Sort results and page through large datasets with OrderBy, OrderByDesc, Limit, and Offset.

## Overview

The query builder exposes `.OrderBy()`, `.OrderByDesc()`, `.Limit()`, and `.Offset()` for sort order and result paging. `.OrderBy()` and `.OrderByDesc()` are available on regular queries only. `.Limit()` and `.Offset()` work on regular queries and JOIN queries. Set-operation builders support `.Limit()` and `.Offset()` on the combined result but not sorting.

## Sorting

### Ascending Order

Pass an array of columns to `.OrderBy()`. PostgreSQL sorts every listed column in ascending order.

```csharp
// sort by Name ascending, then by CreatedAt ascending
List<User> users = await User.Query()
    .OrderBy(x => new object?[] { x.Name, x.CreatedAt })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "name" ASC, "created_at" ASC
```

### Descending Order

```csharp
// sort by Score descending
List<User> topScorers = await User.Query()
    .OrderByDesc(x => new object?[] { x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC
```

### Combining Ascending and Descending

The builder holds a single ORDER BY clause: `.OrderBy()` and `.OrderByDesc()` each replace it, and the call's direction applies to every column in the array. Calling them more than once does not append; the last call wins. To sort columns in mixed directions, pass one array to a single `.OrderBy()` call and wrap individual columns with the `OrderBy.Desc(...)` / `OrderBy.Asc(...)` helpers (from `Socigy.OpenSource.DB.Core.SyntaxHelper.DB`).

```csharp
using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

// sort by Score DESC, then by Name ASC
List<User> results = await User.Query()
    .OrderBy(x => new object?[] { OrderBy.Desc(x.Score), x.Name })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC, "name"
```

## Pagination with Limit and Offset

### Limit

`.Limit(n)` emits a SQL `LIMIT` clause and caps the number of rows returned. `.Top(n)` is an alias.

```csharp
// fetch at most 20 rows
List<User> page = await User.Query()
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

### Offset

`.Offset(n)` emits a SQL `OFFSET` clause and skips the first `n` rows. It is almost always used with `.Limit()`.

```csharp
// fetch page 3 (zero-based pages of 20)
int pageSize = 20;
int pageNumber = 2; // 0-indexed, page 3

List<User> page3 = await User.Query()
    .OrderBy(x => new object?[] { x.CreatedAt })
    .Limit(pageSize)
    .Offset(pageNumber * pageSize)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "created_at" ASC LIMIT 20 OFFSET 40
```

> **WARNING** Always pair `Offset` with `OrderBy`. Without a deterministic sort order the database may return rows in any sequence, making page boundaries unpredictable and risking rows that appear on multiple pages or none at all.

## Cursor-Based Pagination

Offset pagination slows down on large tables because PostgreSQL scans and skips the first `n` rows on every request. Cursor-based pagination avoids this by filtering on the last-seen key value.

```csharp
// cursor-based next page using the last seen Id
Guid lastSeenId = previousPage.Last().Id;

List<User> nextPage = await User.Query(x => x.Id > lastSeenId)
    .OrderBy(x => new object?[] { x.Id })
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

> **TIP** Cursor-based pagination needs a stable, sortable, unique column (an auto-incrementing integer or a time-ordered UUID). It cannot jump to arbitrary pages, but it scales to billions of rows where `OFFSET` would time out.

## Full Pagination Example

```csharp
using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

// typical paged API endpoint
async Task<List<User>> GetPageAsync(DbConnection conn, int page, int pageSize)
{
    return await User.Query(x => x.DeletedAt == null)
        .OrderBy(x => new object?[] { OrderBy.Desc(x.CreatedAt), x.Id })
        .Limit(pageSize)
        .Offset(page * pageSize)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync();
}
```

## Pagination on Set Operations

`.Limit()` and `.Offset()` are available on set-operation builders (Union, Intersect, Except, and so on) and apply to the combined result. Sorting on the combined result is not supported. Apply `OrderBy` to each sub-query before combining, or sort in C# after materializing.

```csharp
// paginate a UNION result
var lhs = User.Query(x => x.Active == true);
var rhs = User.Query(x => x.IsPremium == true);

List<User> combined = await lhs.Union(rhs)
    .Limit(20)
    .Offset(0)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## See Also

- [SELECT / Query](/database/0.3.2/querying/reading/select) for the full query builder reference
- [WHERE Operators](/database/0.3.2/querying/reading/where) for filtering rows before sorting
- [Set Operations](/database/0.3.2/querying/reading/set-operations) for Union, Intersect, and Except with pagination