I have such table and need table 2 result. I am trying to select rows with max date grouped by project_id and ordered by id. And result table must have id column. Tried such request:

SELECT MAX(charges.id) as id, 
       "charges"."profile_id", MAX(failed_at) AS failed_at 
FROM "charges" 
GROUP BY "charges"."profile_id" 
ORDER BY "charges"."id" ASC

And have error:

ERROR: column "charges.id" must appear in the GROUP BY clause or be used in an aggregate function)

Example table

Needed result

CodePudding user response：

SELECT charges.*
FROM charges
INNER JOIN 
( 
    SELECT
        profile_id,
        MAX(charges.failed_at) AS MaxFailed_at
    FROM charges
    GROUP BY profile_id
) AS xQ ON charges.profile_id = xQ.profile_id AND charges.failed_at = xQ.MaxFailed_at