user3915795 user3915795 - 7 months ago 48
SQL Question

Get values from first and last row per group

I'm new to Postgres, coming from MySQL and hoping that one of y'all would be able to help me out.

I have a table with three columns:

name
,
week
, and
value
. This table has a record of the names, the week at which they recorded the height, and the value of their height.
Something like this:

Name | Week | Value
------+--------+-------
John | 1 | 9
Cassie| 2 | 5
Luke | 6 | 3
John | 8 | 14
Cassie| 5 | 7
Luke | 9 | 5
John | 2 | 10
Cassie| 4 | 4
Luke | 7 | 4


What I want is a list per user of the value at the minimum week and the max week. Something like this:

Name |minWeek | Value |maxWeek | value
------+--------+-------+--------+-------
John | 1 | 9 | 8 | 14
Cassie| 2 | 5 | 5 | 7
Luke | 6 | 3 | 9 | 5


In Postgres, I use this query:

select name, week, value
from table t
inner join(
select name, min(week) as minweek
from table
group by name)
ss on t.name = ss.name and t.week = ss.minweek
group by t.name
;


However, I receive an error:


column "w.week" must appear in the GROUP BY clause or be used in an aggregate function

Position: 20


This worked fine for me in MySQL so I'm wondering what I'm doing wrong here?

Answer

This is a bit of a pain, because Postgres has the nice window functions first_value() and last_value(), but these are not aggregation functions. So, here is one way:

select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue,
       max(t.week) as maxWeek, max(lastvalue) as lastValue
from (select t.*, first_value(value) over (partition by name order by week) as firstvalue,
             last_value(value) over (partition by name order by week) as lastvalue
      from table t
     ) t
group by t.name;
Comments