Distinct

PRQL doesn’t have a specific distinct keyword. Instead, use group and take 1:

PRQL

from employees
select department
group department (
  take 1
)

SQL

SELECT
  DISTINCT department
FROM
  employees

This also works without a linebreak:

PRQL

from employees
select department
group department (take 1)

SQL

SELECT
  DISTINCT department
FROM
  employees

Selecting from each group

We are be able to select a single row from each group by combining group and sort:

PRQL

# youngest employee from each department
from employees
group department (
  sort age
  take 1
)

SQL

WITH table_1 AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY
        age
    ) AS _expr_0
  FROM
    employees
)
SELECT
  *
FROM
  table_1 AS table_0
WHERE
  _expr_0 <= 1

Note that we can’t always compile to DISTINCT; when the columns in the group aren’t all the available columns, we need to use a window function:

PRQL

from employees
group [first_name, last_name] (take 1)

SQL

WITH table_1 AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY first_name, last_name) AS _expr_0
  FROM
    employees
)
SELECT
  *
FROM
  table_1 AS table_0
WHERE
  _expr_0 <= 1

Roadmap

When using Postgres dialect, we are planning to compile:

# youngest employee from each department
from employees
group department (
  sort age
  take 1
)

… to …

SELECT DISTINCT ON (department) *
FROM employees
ORDER BY department, age