Distinct

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

PRQL

1. from employees
2. select department
3. group department (
4. take 1
5. )

SQL

1. SELECT
2. DISTINCT department
3. FROM
4. employees

This also works without a linebreak:

PRQL

1. from employees
2. select department
3. group department (take 1)

SQL

1. SELECT
2. DISTINCT department
3. FROM
4. employees

Selecting from each group

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

PRQL

1. # youngest employee from each department
2. from employees
3. group department (
4. sort age
5. take 1
6. )

SQL

1. WITH table_1 AS (
2. SELECT
3. *,
4. ROW_NUMBER() OVER (
5. PARTITION BY department
6. ORDER BY
7. age
8. ) AS _expr_0
9. FROM
10. employees
11. )
12. SELECT
13. *
14. FROM
15. table_1 AS table_0
16. WHERE
17. _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

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

SQL

1. WITH table_1 AS (
2. SELECT
3. *,
4. ROW_NUMBER() OVER (PARTITION BY first_name, last_name) AS _expr_0
5. FROM
6. employees
7. )
8. SELECT
9. *
10. FROM
11. table_1 AS table_0
12. WHERE
13. _expr_0 <= 1

Roadmap

When using Postgres dialect, we are planning to compile:

1. # youngest employee from each department
2. from employees
3. group department (
4. sort age
5. take 1
6. )

… to …

1. SELECT DISTINCT ON (department) *
2. FROM employees
3. ORDER BY department, age