Sort

Order rows based on the values of one or more expressions (generally columns).

sort {(+|-) column}

Parameters

• One expression or a tuple of expressions to sort by
• Each expression can be prefixed with:
  • +, for ascending order, the default
  • -, for descending order
• When using prefixes, even a single expression needs to be in a tuple 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_0 AS (
  SELECT
    *,
    substr(first_name, 2, 5) AS _expr_0
  FROM
    employees
)
SELECT
  *
FROM
  table_0
ORDER BY
  _expr_0

Ordering guarantees

Ordering is persistent through a pipeline in PRQL. For example:

from employees
sort tenure
join locations (==employee_id)

SELECT
  employees.*,
  locations.*
FROM
  employees
  INNER JOIN locations ON employees.employee_id = locations.employee_id
ORDER BY
  employees.tenure

Here, PRQL pushes the sort down the pipeline, compiling the ORDER BY to the end of the query. Consequently, most relation transforms retain the row order.

The explicit semantics are:

• sort introduces a new order,
• group resets the order,
• join retains the order of the left relation,
• database tables don’t have a known order.

Comparatively, in SQL, relations possess no order, being orderable solely within the context of the query result, LIMIT statement, or window function. The lack of inherent order can result in an unexpected reshuffling of a previously ordered relation from a JOIN or windowing operation.

For instance, an SQL query such as:

WITH albums_sorted AS (
  SELECT *
  FROM albums
  ORDER BY title
)
SELECT *
FROM albums_sorted
JOIN artists USING (artist_id)

…doesn’t guarantee any row order (indeed — even without the JOIN, the SQL standard doesn’t guarantee an order, although most implementations will respect it).