Selecting last row for unique index ordered by column

user_18894465 0
Viewed 46

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

account_uuid price type created_at (↑)
aa4dd27e-b72a-40fd-bdab-94810e585734 8.96 0 1649840899215
5c5625af-65e5-43d3-a39d-b896cd4d02a3 14.58 0 1649841117203
aa4dd27e-b72a-40fd-bdab-94810e585734 null 2 1649843706217
d8a106f9-dbf2-42f1-ac6b-a17e88700fab 3.939 0 1650434747192
aa4dd27e-b72a-40fd-bdab-94810e585734 14.58 0 1650438658596

Sample Result (Desired)

account_uuid price type (=0) created_at (↑)
5c5625af-65e5-43d3-a39d-b896cd4d02a3 14.58 0 1649841117203
d8a106f9-dbf2-42f1-ac6b-a17e88700fab 3.939 0 1650434747192
aa4dd27e-b72a-40fd-bdab-94810e585734 14.58 0 1650438658596

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

postgresql sql
2 Answers

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

user_110143100

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.

user_2157520
Related