CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Ajouve
Ajouve

Reputation: 10049

Group by on PostgreSQL

I have an application table, each application can have activities, each time an activity is updated there is a log in the activityupdate table.

activityupdate table: - id - activity_id - user_id - date

activity table: - id - application_id - rank - ...

I'd like to get the last activityupdate for all activities of an application order by activity rank

I have

SELECT au
FROM activityupdate au
INNER JOIN activity ac
ON au.activity_id = ac.id
INNER JOIN application ap
ON ac.application_id = ap.id
WHERE ap.id = 3
GROUP BY au, ac.rank
ORDER BY ac.rank ASC

But this doesn't works, I don't know why I have to set au and ac.rank in the GROUP BY

Thanks for your help

Edit, thanks Gordon, I find the solution

select *
from (SELECT au,
      row_number() over (partition by au.activity_id order by au.date desc) as seqnum
      FROM activityupdate au INNER JOIN
      activity ac
      ON au.activity_id = ac.id INNER JOIN
      application ap
      ON ac.application_id = ap.id
  WHERE ap.id = 3
  ORDER BY ac.rank ASC
 ) t
where seqnum = 1

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You don't need an aggregation. The function row_number() does what you need. It assigns a sequential number to rows within a group (defined by the partition by clause). The order of the numbers is determined by the order by.

You can then choose the most recent using a where clause:

select *
from (SELECT au,
             row_number() over (partition by au order by rank desc) as seqnum
      FROM activityupdate au INNER JOIN
           activity ac
           ON au.activity_id = ac.id INNER JOIN
           application ap
           ON ac.application_id = ap.id
      WHERE ap.id = 3
     ) t
where seqnum = 1

Upvotes: 2

Related Questions