CODEWITHSUNDEEP
sqlpostgresql
Pascal
Pascal

Reputation: 2154

PostgreSQL group by column with aggregate

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

Upvotes: 2

Views: 848

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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;

screen capture from demo link below

Demo

Upvotes: 0

user330315
user330315

Reputation:

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)

Upvotes: 0

Related Questions