How do I: remove duplicates?

PRQL doesn’t have a specific distinct keyword. Instead duplicate tuples in a relation can be removed by using group and take 1:

PRQL

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

SQL

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

This also works with a wildcard:

PRQL

1. from employees
2. group employees.* (take 1)

SQL

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

Remove duplicates from each group?

To select a single row from each group group can be combined with sort and take:

PRQL

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

SQL

1. WITH table_0 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_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_0 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_0
12. WHERE
13. _expr_0 <= 1