CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
user2974739
user2974739

Reputation: 639

Postgresql get max row group by column

I am trying to get the max row from the sum of daily counts in a table. I have looked at several posts that look similar, however it doesn't seem to work. I have tried to follow Get MAX row for GROUP in MySQL but it doesn't work in Postgres. Here's what I have

select source, SUM(steps) as daily_steps, to_char("endTime"::date, 'MM/DD/YYYY') as step_date
 from activities
 where user_id = 1
 and "endTime" <= CURRENT_TIMESTAMP + INTERVAL '1 day'
 and "endTime" >= CURRENT_TIMESTAMP - INTERVAL '7 days' 
 group by source, to_char("endTime"::date, 'MM/DD/YYYY')

This returns the following

source, daily_steps, step_date
"walking";750;"11/17/2015"
"walking";821;"11/22/2015"
"walking";106;"11/20/2015"
"running";234;"11/21/2015"
"running";600;"11/24/2015"

I would like the result to return only the rows that have the max value for daily_steps by source. The result should look like

source, daily_steps, step_date
"walking";821;"11/22/2015"
"running";600;"11/24/2015"

Upvotes: 1

Views: 185

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Postgres offers the convenient distinct on syntax:

select distinct on (a.source) a.*
from (select source, SUM(steps) as daily_steps, to_char("endTime"::date, 'MM/DD/YYYY') as step_date
      from activities a
      where user_id = 1 and
            "endTime" <= CURRENT_TIMESTAMP + INTERVAL '1 day' and
            "endTime" >= CURRENT_TIMESTAMP - INTERVAL '7 days' 
      group by source, to_char("endTime"::date, 'MM/DD/YYYY')
     ) a
order by a.source, daily_steps desc;

Upvotes: 1

Related Questions