I need to group by id and select the task with min/max seq as start and end
id | task | seq
----+------+-----
1 | aaa | 1
1 | bbb | 2
1 | ccc | 3
SELECT
id,
CASE WHEN seq = MIN(seq) THEN task AS start,
CASE WHEN seq = MAX(seq) THEN task AS end
FROM table
GROUP BY id;
But this results in
ERROR: column "seq" must appear in the GROUP BY clause or be used in an aggregate function
But I do not want group by seq
One method uses arrays:
SELECT id,
(ARRAY_AGG(task ORDER BY seq ASC))[1] as start_task,
(ARRAY_AGG(task ORDER BY seq DESC))[1] as end_task
FROM table
GROUP BY id;
Another method uses window functions with SELECT DISTINCT:
select distinct id,
first_value(task) over (partition by id order by seq) as start_task,
first_value(task) over (partition by id order by seq desc) as end_task
from t;
You can use window functions with a derived table:
select id, task, min_seq as start, max_seq as "end"
from (
select id, task, seq,
max(seq) over (partition by id) as max_seq,
min(seq) over (partition by id) as min_seq
from the_table
) t
where seq in (max_seq, min_seq)
One option here would be to use ROW_NUMBER along with aggregation and pivoting logic:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq) rn_min,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq DESC) rn_max
FROM yourTable
)
SELECT
id,
MAX(CASE WHEN rn_min = 1 THEN task END) AS start,
MAX(CASE WHEN rn_max = 1 THEN task END) AS end
FROM cte
GROUP BY
id;
