Get values from first and last row per group

Question

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 1

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;