Sort

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

sort {(+|-) column}

Parameters

• One expression or a list of expression 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 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_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:

PRQL

from employees
sort tenure
join locations (==employee_id)

SQL

SELECT
  employees.*,
  locations.*
FROM
  employees
  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.

Info

To be precise — in PRQL, a relation is an array of tuples and not a set or a bag. The persistent nature of this order remains intact through sub-queries and intermediate table definitions.

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).