Objective

I have a list of payment records that contain account_uuid, price, type & created_at. I need to get a list of the latest payment record specific to each account_uuid where the type = 0.

What I have tried

My first attempt was to ORDER BY on created_at to ensure the latest row was last, then to GROUP BY on account_uuid. The issue is that I would have to add both account_uuid and created_at to the GROUP BY expression which would include multiple records for the account_uuid as it will only group rows when both account_uuid and created_at are the same, which is never.

My second attempt was to SELECT DISTINCT ON account_uuid. This didn't work for the same reason above as it complains I must include my ORDER BY column in my DISTINCT ON expression which would yield the same result.

Sample Data

Sample Result (Desired)

Problem / Question

What I am trying to achieve is the sample result which you can see returns only the latest row for the account_uuid where type is 0 and created_at is ascending. Best case I would like to do it without any joins/subqueries but am happy for just getting it working.

Thank You

CodePudding user response：

Edit: A simpler solution would be:

select distinct on (account_uuid) 
       max(created_at) 
       ,price
       ,type
       ,account_uuid
from tableName
where type = 0
group by price,type,account_uuid

You can achieve this in Postgres without using join/sub-queries by using a combination of DISTINCT ON and window functions. This assumes that you truly want the "latest row where type = 0" and not "If the latest row = 0".

select distinct on (account_uuid) 
       max(created_at) over (partition by account_uuid order by created_at desc)
       ,price
       ,type
       ,account_uuid
from tableName
where type = 0

Fiddle here

CodePudding user response：

You need select using ROW_NUMBER() in a sub query and then in the outer where statement you select RN = 1

There are many code examples of using ROW_NUMBER() if you search for it.