# Sorting and Pagination

This page shows how `orderBy()`, `limit()`, `offset()`, and `total()` combine into a paginated query.

## `orderBy()`

```typescript
MySQLPool.table("users").orderBy("created_at", "DESC").orderBy("id");
// ... ORDER BY `created_at` DESC, `id` ASC
```

| Rule | Detail |
|---|---|
| Default direction | `ASC` |
| Case | The direction is uppercased first, so `"desc"` equals `"DESC"` |
| Column name | Backtick-quoted when it contains no `.` |
| Multiple calls | Joined with commas in call order |

## `limit()` and `offset()`

```typescript
const page = 3;
const perPage = 20;

MySQLPool
  .table("users")
  .limit(perPage)
  .offset((page - 1) * perPage);
// ... LIMIT 20 OFFSET 40
```

## `total()`

`total()` wraps the query in a subquery and uses `COUNT(*) OVER()` so every row carries the total number of matching rows (`src/MySQLPool.ts:254-256`), removing the extra `COUNT(*)` query from pagination.

```typescript
interface UserRow {
  total: number;
  id: number;
  name: string;
}

const rows = await MySQLPool
  .table("users")
  .select("id", "name")
  .where("status", "active")
  .total()
  .orderBy("id", "DESC")
  .limit(10)
  .offset(0)
  .get<UserRow>();

const total = rows[0]?.total ?? 0;
```

Generated SQL:

```sql
SELECT COUNT(*) OVER() AS total, data.*
FROM (SELECT `id`, `name` FROM `users` WHERE `status` = ?) AS data
ORDER BY `id` DESC LIMIT 10 OFFSET 0
```

## Related Pages

- [Select and Where](/select-and-where)
- [Builder State](/builder-state)