GROUP BY query that works in MySQL rejected by PostgreSQL

Question

I have this query (which works fine in MySQL):

SELECT units.*, activity_logs.created_at 
FROM "units" left join activity_logs on units.id=activity_logs.unit_id 
GROUP BY units.id 
ORDER BY activity_logs.created_at desc

Postgres gives this error:

PG::Error: ERROR:  column "activity_logs.created_at" must appear in the GROUP BY clause or be used in an aggregate function

I know that Postgres is correctly SQL-wise, but how do I get what I want? Is this the last activity_log entry for each block combined with the entire unit table?

Thanks in advance!

• Jacob

Answer 1

The error explains this: you need to use an aggregate function to make it deterministic, for example:

SELECT units.*, max(activity_logs.created_at)
FROM "units" left join activity_logs on units.id=activity_logs.unit_id 
GROUP BY units.id 
ORDER BY max(activity_logs.created_at) desc;