Sort

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

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

PRQL

1. from employees
2. sort age

SQL

1. SELECT
2. *
3. FROM
4. employees
5. ORDER BY
6. age

PRQL

1. from employees
2. sort {-age}

SQL

1. SELECT
2. *
3. FROM
4. employees
5. ORDER BY
6. age DESC

PRQL

1. from employees
2. sort {age, -tenure, +salary}

SQL

1. SELECT
2. *
3. FROM
4. employees
5. ORDER BY
6. age,
7. tenure DESC,
8. salary

We can also use expressions:

PRQL

1. from employees
2. sort {s"substr({first_name}, 2, 5)"}

SQL

1. WITH table_0 AS (
2. SELECT
3. *,
4. substr(first_name, 2, 5) AS _expr_0
5. FROM
6. employees
7. )
8. SELECT
9. *
10. FROM
11. table_0
12. ORDER BY
13. _expr_0

Ordering guarantees

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

PRQL

1. from employees
2. sort tenure
3. join locations (==employee_id)

SQL

1. SELECT
2. employees.*,
3. locations.*
4. FROM
5. employees
6. JOIN locations ON employees.employee_id = locations.employee_id
7. ORDER BY
8. 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:

1. WITH albums_sorted AS (
2. SELECT *
3. FROM albums
4. ORDER BY title
5. )
6. SELECT *
7. FROM albums_sorted
8. 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).