Interface OrderByInterface<DB, TB, O>

Clears the order by clause from the query.

See orderBy for adding an order by clause or item to a query.

Examples

const query = db
  .selectFrom('person')
  .selectAll()
  .orderBy('id', 'desc')

const results = await query
  .clearOrderBy()
  .execute()
Copy

The generated SQL (PostgreSQL):

select * from "person"
Copy

Adds an order by clause to the query.

orderBy calls are additive. Meaning, additional orderBy calls append to the existing order by clause.

orderBy is supported in select queries on all dialects. In MySQL, you can also use orderBy in update and delete queries.

In a single call you can add a single column/expression or multiple columns/expressions.

Single column/expression calls can have 1-2 arguments. The first argument is the expression to order by, while the second optional argument is the direction (asc or desc), a callback that accepts and returns an OrderByItemBuilder or an expression.

See clearOrderBy to remove the order by clause from a query.

Examples

Single column/expression per call:

await db
  .selectFrom('person')
  .select('person.first_name as fn')
  .orderBy('id')
  .orderBy('fn', 'desc')
  .execute()
Copy

The generated SQL (PostgreSQL):

select "person"."first_name" as "fn"
from "person"
order by "id", "fn" desc
Copy

Building advanced modifiers:

await db
  .selectFrom('person')
  .select('person.first_name as fn')
  .orderBy('id', (ob) => ob.desc().nullsFirst())
  .execute()
Copy

The generated SQL (PostgreSQL):

select "person"."first_name" as "fn"
from "person"
order by "id" desc nulls first
Copy

The order by expression can also be a raw sql expression or a subquery in addition to column references:

import { sql } from 'kysely'

await db
  .selectFrom('person')
  .selectAll()
  .orderBy((eb) => eb.selectFrom('pet')
    .select('pet.name')
    .whereRef('pet.owner_id', '=', 'person.id')
    .limit(1)
  )
  .orderBy(
    sql<string>`concat(first_name, last_name) asc`
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
order by
  ( select "pet"."name"
    from "pet"
    where "pet"."owner_id" = "person"."id"
    limit $1
  ) asc,
  concat(first_name, last_name) asc
Copy

dynamic.ref can be used to refer to columns not known at compile time:

async function someQuery(orderBy: string) {
  const { ref } = db.dynamic

  return await db
    .selectFrom('person')
    .select('person.first_name as fn')
    .orderBy(ref(orderBy))
    .execute()
}

someQuery('fn')
Copy

The generated SQL (PostgreSQL):

select "person"."first_name" as "fn"
from "person"
order by "fn"
Copy