> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# orderBy

> Sort query results

`orderBy` is a method for sorting query results by specific columns. You can sort in ascending (ASC) or descending (DESC) order, and can sort by multiple columns.

## Basic Usage

### Ascending (ASC)

```typescript theme={null}
const users = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name" })
  .orderBy("users.name", "asc");

// ORDER BY users.name ASC
```

### Descending (DESC)

```typescript theme={null}
const posts = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title", created_at: "posts.created_at" })
  .orderBy("posts.created_at", "desc");

// ORDER BY posts.created_at DESC
```

## Multiple Column Sorting

You can sort by multiple columns by chaining `orderBy`.

```typescript theme={null}
const users = await puri
  .table("users")
  .select({ id: "users.id", role: "users.role", name: "users.name" })
  .orderBy("users.role", "asc") // First: role ascending
  .orderBy("users.name", "asc"); // Second: name ascending

// ORDER BY users.role ASC, users.name ASC
```

**Sort Priority:**

1. First `orderBy` - Primary sort
2. Second `orderBy` - Secondary sort (when primary is same)
3. Third `orderBy` - Tertiary sort (when secondary is also same)

## NULL Sort Order (nulls)

The third argument `nulls` specifies where NULL values should be placed in the sort order.

```typescript theme={null}
// NULL at the end
const users = await puri
  .table("users")
  .select({ id: "users.id", name: "users.name", last_login: "users.last_login" })
  .orderBy("users.last_login", "desc", "last");

// ORDER BY users.last_login DESC NULLS LAST
```

```typescript theme={null}
// NULL first
const posts = await puri
  .table("posts")
  .select({ id: "posts.id", title: "posts.title", deleted_at: "posts.deleted_at" })
  .orderBy("posts.deleted_at", "asc", "first");

// ORDER BY posts.deleted_at ASC NULLS FIRST
```

**`nulls` options:**

| Value     | Description                                                                  |
| --------- | ---------------------------------------------------------------------------- |
| `"first"` | Place NULL values at the beginning of results                                |
| `"last"`  | Place NULL values at the end of results                                      |
| omitted   | Database default behavior (PostgreSQL: ASC → NULLS LAST, DESC → NULLS FIRST) |

## Array-style orderBy

You can pass multiple sort conditions as an array in a single call.

```typescript theme={null}
const products = await puri
  .table("products")
  .select({
    id: "products.id",
    name: "products.name",
    price: "products.price",
    rating: "products.rating",
  })
  .orderBy([
    { column: "products.rating", order: "desc", nulls: "last" },
    { column: "products.price", order: "asc" },
  ]);

// ORDER BY products.rating DESC NULLS LAST, products.price ASC
```

Each array item can be one of the following:

* **string**: Column name only (defaults to ASC)
* **SqlExpression**: SQL expression
* **object**: `{ column, order?, nulls? }` for detailed specification

```typescript theme={null}
// Mix different forms
.orderBy([
  "products.featured",                                         // string: ASC
  { column: "products.rating", order: "desc" },                // object: DESC
  { column: "products.price", order: "asc", nulls: "last" },  // object + nulls
])
```

## Practical Examples

<Tabs>
  <Tab title="Latest First" icon="clock">
    ```typescript theme={null}
    // Latest posts first
    const posts = await puri.table("posts")
      .select({
        id: "posts.id",
        title: "posts.title",
        created_at: "posts.created_at"
      })
      .orderBy("posts.created_at", "desc")
      .limit(20);

    // Most recently updated first
    const users = await puri.table("users")
      .select({
        id: "users.id",
        name: "users.name",
        updated_at: "users.updated_at"
      })
      .orderBy("users.updated_at", "desc");
    ```
  </Tab>

  <Tab title="Alphabetical" icon="arrow-down-a-z">
    ```typescript theme={null}
    // Names in alphabetical order
    const users = await puri.table("users")
      .select({
        id: "users.id",
        name: "users.name"
      })
      .orderBy("users.name", "asc");

    // Titles in reverse alphabetical order
    const products = await puri.table("products")
      .select({
        id: "products.id",
        name: "products.name"
      })
      .orderBy("products.name", "desc");
    ```
  </Tab>

  <Tab title="Complex Sorting" icon="layer-group">
    ```typescript theme={null}
    // Priority: status → created date
    const orders = await puri.table("orders")
      .select({
        id: "orders.id",
        status: "orders.status",
        created_at: "orders.created_at"
      })
      .orderBy("orders.status", "asc")        // pending → processing → completed
      .orderBy("orders.created_at", "desc");  // Latest first

    // Priority: featured → rating → sales
    const products = await puri.table("products")
      .select({
        id: "products.id",
        name: "products.name",
        featured: "products.featured",
        rating: "products.rating",
        sales: "products.sales"
      })
      .orderBy("products.featured", "desc")  // Featured first
      .orderBy("products.rating", "desc")    // High ratings
      .orderBy("products.sales", "desc");    // High sales
    ```
  </Tab>

  <Tab title="Numeric Sorting" icon="arrow-down-1-9">
    ```typescript theme={null}
    // Lowest price first
    const products = await puri.table("products")
      .select({
        id: "products.id",
        name: "products.name",
        price: "products.price"
      })
      .where("products.published", true)
      .orderBy("products.price", "asc");

    // Highest views first
    const posts = await puri.table("posts")
      .select({
        id: "posts.id",
        title: "posts.title",
        views: "posts.views"
      })
      .orderBy("posts.views", "desc")
      .limit(10);
    ```
  </Tab>

  <Tab title="NULL Handling" icon="ban">
    ```typescript theme={null}
    // Use the nulls option to explicitly control NULL placement
    const users = await puri.table("users")
      .select({
        id: "users.id",
        name: "users.name",
        last_login: "users.last_login"
      })
      .orderBy("users.last_login", "desc", "last");  // NULL at the end

    // NULL first
    const posts = await puri.table("posts")
      .select({
        id: "posts.id",
        title: "posts.title",
        deleted_at: "posts.deleted_at"
      })
      .orderBy("posts.deleted_at", "asc", "first");  // NULL comes first
    ```
  </Tab>
</Tabs>

## Sorting Joined Tables

You can also sort by columns from joined tables.

```typescript theme={null}
const posts = await puri
  .table("posts")
  .join("users", "posts.user_id", "users.id")
  .select({
    post_id: "posts.id",
    title: "posts.title",
    author_name: "users.name",
  })
  .orderBy("users.name", "asc") // By author name
  .orderBy("posts.created_at", "desc"); // Latest first for same author

// ORDER BY users.name ASC, posts.created_at DESC
```

## Sorting by SELECT Alias

You can also sort by aliases specified in SELECT.

```typescript theme={null}
const posts = await puri
  .table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    view_count: "posts.views", // Alias specified
  })
  .orderBy("view_count", "desc"); // Using alias

// SELECT posts.views as view_count
// ORDER BY view_count DESC
```

## Sorting Aggregate Functions

You can sort by aggregate function results with GROUP BY.

```typescript theme={null}
const userStats = await puri
  .table("posts")
  .select({
    user_id: "posts.user_id",
    post_count: Puri.count(),
  })
  .groupBy("posts.user_id")
  .orderBy("post_count", "desc") // Most posts first
  .limit(10);

// SELECT user_id, COUNT(*) as post_count
// FROM posts
// GROUP BY user_id
// ORDER BY post_count DESC
// LIMIT 10
```

## Sorting by SQL Expressions

`orderBy` accepts `SqlExpression<"number">` or `SqlExpression<"string">` instead of a column name. This is useful when sorting by computed values such as full-text search rankings or similarity scores.

### Sorting by Search Rank

```typescript theme={null}
// Sort full-text search results by relevance using ts_rank
const posts = await puri
  .table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    rank: Puri.tsRank("posts.content_tsv", "typescript"),
  })
  .orderBy(Puri.tsRank("posts.content_tsv", "typescript"), "desc");

// ORDER BY ts_rank(posts.content_tsv, plainto_tsquery('simple', ?)) DESC
```

### Sorting by Similarity Score

```typescript theme={null}
// Sort by pg_trgm similarity score
const users = await puri
  .table("users")
  .select({
    id: "users.id",
    name: "users.name",
    score: Puri.similarity("users.name", "John"),
  })
  .orderBy(Puri.similarity("users.name", "John"), "desc");

// ORDER BY similarity(users.name, ?) DESC
```

### Custom Sorting with rawNumber

```typescript theme={null}
// Sort by a custom SQL expression
const products = await puri
  .table("products")
  .select({
    id: "products.id",
    name: "products.name",
    weighted: Puri.rawNumber("(products.views * 0.3 + products.likes * 0.7)"),
  })
  .orderBy(Puri.rawNumber("(products.views * 0.3 + products.likes * 0.7)"), "desc");

// ORDER BY (products.views * 0.3 + products.likes * 0.7) DESC
```

<Info>
  When using SQL expressions, `orderByRaw` is used internally and parameter bindings are
  automatically applied.
</Info>

## Sort Directions

### ASC (Ascending)

* Numbers: small → large
* Strings: A → Z
* Dates: past → future
* NULL: last

```typescript theme={null}
.orderBy("users.age", "asc")
// 18, 25, 30, 35, 40, NULL
```

### DESC (Descending)

* Numbers: large → small
* Strings: Z → A
* Dates: future → past
* NULL: last

```typescript theme={null}
.orderBy("users.age", "desc")
// 40, 35, 30, 25, 18, NULL
```

<Info>**Default is ASC**. If direction is not specified, it sorts in ascending order.</Info>

## Performance Optimization

### 1. Use Indexes

```typescript theme={null}
// ✅ Good: Sort by indexed column
.orderBy("users.created_at", "desc")  // Index needed on created_at

// ❌ Bad: Sort by non-indexed column (slow)
.orderBy("users.bio", "asc")
```

**Recommended Indexes:**

```sql theme={null}
CREATE INDEX idx_users_created_at ON users(created_at DESC);
CREATE INDEX idx_posts_published_created ON posts(published, created_at DESC);
```

### 2. Use with LIMIT

```typescript theme={null}
// ✅ Good: Sort only top N
.orderBy("posts.views", "desc")
.limit(10)  // Only top 10 instead of sorting all

// ⚠️ Caution: Without LIMIT, sorts all rows (slow)
.orderBy("posts.views", "desc")
```

### 3. Composite Indexes

When sorting by multiple columns, composite indexes are needed.

```typescript theme={null}
// For this query
.orderBy("products.category", "asc")
.orderBy("products.price", "asc")

// This index is needed
CREATE INDEX idx_products_category_price ON products(category, price);
```

## Cautions

### 1. Sort Order Matters

```typescript theme={null}
// Order of orderBy matters

// ✅ Correct: status → date
.orderBy("orders.status", "asc")
.orderBy("orders.created_at", "desc")

// ❌ Different from intention: date → status
.orderBy("orders.created_at", "desc")
.orderBy("orders.status", "asc")
```

### 2. NULL Values

```typescript theme={null}
// NULL sorting may differ between PostgreSQL and MySQL

// PostgreSQL: NULL LAST (default)
.orderBy("users.last_login", "desc")

// Use the nulls option to specify explicitly
.orderBy("users.last_login", "desc", "first")  // NULLS FIRST
.orderBy("users.last_login", "desc", "last")   // NULLS LAST
```

### 3. Case Sensitivity

```typescript theme={null}
// ⚠️ Caution: Case-sensitive sorting
.orderBy("users.name", "asc")
// "Alice", "Bob", "alice", "bob"

// To ignore case
.orderBy(puri.raw("LOWER(users.name)"), "asc")
// "Alice", "alice", "Bob", "bob"
```

### 4. Performance Impact

```typescript theme={null}
// ❌ Bad: Sort entire large dataset
const allUsers = await puri.table("users").orderBy("users.created_at", "desc"); // Sort millions of rows

// ✅ Good: Limit with LIMIT
const recentUsers = await puri.table("users").orderBy("users.created_at", "desc").limit(100); // Only top 100
```

## Type Safety

`orderBy` validates columns in a type-safe manner.

```typescript theme={null}
// ✅ Valid column
await puri.table("users").orderBy("users.created_at", "desc");

// ❌ Type error: Non-existent column
await puri.table("users").orderBy("users.unknown_field", "desc");

// ❌ Type error: Invalid direction
await puri.table("users").orderBy("users.name", "invalid"); // Only "asc" or "desc" allowed
```

## Next Steps

<CardGroup cols={2}>
  <Card title="limit" icon="hashtag" href="/en/api-reference/puri-methods/limit">
    Limit result count
  </Card>

  <Card title="where" icon="filter" href="/en/api-reference/puri-methods/where">
    Filter with conditions
  </Card>

  <Card title="select" icon="list-check" href="/en/api-reference/puri-methods/select">
    Select fields to query
  </Card>

  <Card title="join" icon="link" href="/en/api-reference/puri-methods/join">
    Join tables
  </Card>
</CardGroup>