select newest update record among similar record

Question

I have 2 tables segments and summaries.
I want to list the shows in a date interval and just show the latest updated segments (in bold) since it is the freshest copy of the segment for that person.
Here is the query I used to have the result below:

select seg.summ, su.channel, seg.block_id, seg.person, seg.updated_at as segupdate 
from segments seg 
  left join summaries su on seg.summ = su.id 
where su.created_at between '2018-06-06' and '2018-07-13' 
order by summ, block_id, seg.updated_at desc;   

I want this:

How can I get change my query to show just the bolded rows? to remove the duplicates that are an older version?

Answer 1

I'm not super familiar with Postgres, but based on some Internet searches, it seems that your version of Postgres supports 'window functions'. It also seems to support 'common table expressions'. If those statements are true, you should be able to wrap you main query into a common table expression and utilize the ROW_NUMBER window function to assign a row number to each 'grouping' (partition by person) and order by seg.updated desc. Something like the following

;
With BaseData as
(
SELECT seg.summ
    ,su.channel
    ,seg.block_id
    ,seg.person
    ,seg.updated_at AS segupdate
    ,ROW_NUMBER() OVER(PARTITION BY seg.person ORDER BY seg.updated_at desc) as rn
FROM segments seg
LEFT JOIN summaries su
    ON seg.summ = su.id
WHERE su.created_at BETWEEN '2018-06-06'
        AND '2018-07-13'
ORDER BY summ
    ,block_id
    ,seg.updated_at DESC;
)
SELECT * FROM BaseData where rn = 1

Answer 2

This is what I did :

select distinct on (seg.summ, seg.block_id, seg.person) su.channel, seg.updated_at as segupdate from segments seg left join summaries su on seg.summ = su.id where su.created_at between '2018-06-06' and '2018-07-13' order by summ, block_id, person, seg.updated_at desc;