Sort

Orders rows based on the values of one or more columns.

sort [{direction}{column}]

Parameters

• One column or a list of columns to sort by
• Each column can be prefixed with:
  • +, for ascending order, the default
  • -, for descending order
• When using prefixes, even a single column needs to be in a list or parentheses. (Otherwise, sort -foo is parsed as a subtraction between sort and foo.)

Examples

from employees
sort age

SELECT
  *
FROM
  employees
ORDER BY
  age

from employees
sort [-age]

SELECT
  *
FROM
  employees
ORDER BY
  age DESC

from employees
sort [age, -tenure, +salary]

SELECT
  *
FROM
  employees
ORDER BY
  age,
  tenure DESC,
  salary

We can also use expressions:

from employees
sort [s"substr({first_name}, 2, 5)"]

WITH table_1 AS (
  SELECT
    *,
    substr(first_name, 2, 5) AS _expr_0
  FROM
    employees
  ORDER BY
    _expr_0
)
SELECT
  *
FROM
  table_1 AS table_0

Notes

Ordering guarantees

Most DBs will persist ordering through most transforms; for example, you can expect this result to be ordered by tenure.

from employees
sort tenure
derive name = f"{first_name} {last_name}"

SELECT
  *,
  CONCAT(first_name, ' ', last_name) AS name
FROM
  employees
ORDER BY
  tenure

But:

• This is an implementation detail of the DB. If there are instances where this doesn’t hold, please open an issue, and we’ll consider how to manage it.
• Some transforms which change the existence of rows, such as join or group, won’t persist ordering; for example:

from employees
sort tenure
join locations [==employee_id]

WITH table_1 AS (
  SELECT
    *
  FROM
    employees
  ORDER BY
    tenure
)
SELECT
  table_0.*,
  locations.*
FROM
  table_1 AS table_0
  JOIN locations ON table_0.employee_id = locations.employee_id

See Issue #1363 for more details.