0

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;   

enter image description here

I want this:

enter image description here

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

1
  • 1
    Unrelated, but: Postgres 9.1 has been out of support for over two years now. You should really plan an upgrade to a supported version now. – a_horse_with_no_name Sep 27 '18 at 8:31
0

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
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.