sql - How to do a Postgresql group aggregation: 2 fields using one to select the other -

sql - How to do a Postgresql group aggregation: 2 fields using one to select the other -

i have table - info - of rows, simplified, so:

name,amount,last,date a,16,31,1-jan-2014 a,27,38,1-feb-2014 a,12,34,1-mar-2014 b,8,37,1-jan-2014 b,3,38,1-feb-2014 b,17,39,1-mar-2014

i wish grouping them similar to:

select name,sum(amount),aggr(last),max(date) info grouping name

for aggr(last) want value of 'last' row contains max(date)

so result want 2 rows

name,amount,last,date a,55,34,1-mar-2014 b,28,39,1-mar-2014

i.e. in both cases, value of lastly 1 row contained 1-mar-2014

the query i'm doing same, many more sum() fields , millions of rows, i'm guessing aggregate function avoid multiple requests each grouping of incoming rows.

instead, utilize row_number() , conditional aggregation:

select name, sum(amount), max(case when seqnum = 1 lastly end) last, max(date) (select d.*, row_number() on (partition name order date desc) seqnum info d ) d grouping name;

sql postgresql aggregate-functions