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

PRQL

from employees
sort age

SQL

SELECT
  *
FROM
  employees
ORDER BY
  age

PRQL

from employees
sort [-age]

SQL

SELECT
  *
FROM
  employees
ORDER BY
  age DESC

PRQL

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

SQL

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

We can also use expressions:

PRQL

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

SQL

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

Notes

Ordering guarantees

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

PRQL

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

SQL

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:

PRQL

from employees
sort tenure
join locations [==employee_id]

SQL

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

See Issue #1363 for more details.