in postgresql, can I combine this into a single query?

Question

I have the below table of updates.....

refer	event_date	column detail	event_type	cat1
2	yesterday	abc	type 3	cat x
2	last week	abc3	type 11	cat b
2	today	abc123	type 4	cat a
2	last month	xyz	type 22	cat z
2	last year	wtf	type 11	cat z

so for refer = 2

abc123 is the latest update based on latest date.
abc3 is the latest update for event_type 11 and cat = b
xyz is the latest update for cat z

Is it possible to do this in a single query? the only way I can get my results is to use separate queries or cte's:

with cte1 as (
select  t.refer,
        t.detail
from    
        (
            select ch.refer,
                  ch.detail,
                    row_number() over (partition by refer order by event_date desc) as rn
            from    table  ch
            ) as latest
    where ch.rn = 1
)
cte2 as(
select  t.refer,
        t.detail
from    
        (
            select ch.refer,
                  ch.detail,
                    row_number() over (partition by refer order by event_date desc) as rn
            from    table  ch
            where   event_type = '11'
            and     cat = 'b'
            ) as latest
    where ch.rn = 1
)
cte3 as(
select  t.refer,
        t.detail
from    
        (
            select ch.ref,
                  ch.detail,
                    row_number() over (partition by refer order by event_date desc) as rn
            from    table  ch
            where   event_type = 'z'   
            ) as latest
    where ch.rn = 1
    and cat = 'z'
);

All three are searching over different sets of rows, so I would say "no". However, you can compress that big query quite a bit. — The Impaler, May 16 '23 at 15:11

GMB · Accepted Answer · 2023-05-16T15:25:17.267

We can enumerate the three partitions with three row_numbers, then pivot with conditional aggregation:

select refer,
    max(detail) filter(where rn1 = 1                                  ) detail,
    max(detail) filter(where rn2 = 1 and cat = 'z'                    ) detail2,
    max(detail) filter(where rn3 = 1 and cat = 'b' and event_type = 11) detail3
from (
    select t.*,
        row_number() over(partition by refer                  order by event_date desc) rn1,
        row_number() over(partition by refer, cat             order by event_date desc) rn2,
        row_number() over(partition by refer, event_type, cat order by event_date desc) rn3
    from mytable t
) t
group by refer

refer	detail	detail2	detail3
2	abc123	xyz	abc3

fiddle

Of course this assumes that you are storing your dates in a date-like datatype, not as strings such as 'today' or 'last year'.

Alternatively, if you want the results as rows rather than as columns, then we don't need aggregation:

select *
from (
    select t.*,
        row_number() over(partition by refer                  order by event_date desc) rn1,
        row_number() over(partition by refer, cat             order by event_date desc) rn2,
        row_number() over(partition by refer, event_type, cat order by event_date desc) rn3
    from mytable t
) t
where
    rn1 = 1
    or (rn2 = 1 and cat = 'z')
    or (rn3 = 1 and cat = 'b' and event_type = 11)

This looks good, and answers the question about creating a single query, I must find out more about FILTERing to as the doucmentation isnt clear. Now to see how it performs....... — earbasher, May 16 '23 at 15:27
@TheImpaler: welcome. It happens to me on a regular basis :), as yesterday in: https://stackoverflow.com/questions/76257557/unpivot-multiple-measures-columns-in-snowflake-fact-table/76257669#76257669 — GMB, May 16 '23 at 15:58
@GMB i forgot to mentoin that column_detail also has nulls in it which means that i need to add a not null to each row eg row_number() over(partition by refer (where refer is not null) order by event_date desc) rn1, row_number() over(partition by refer, cat (where refer is not null) order by event_date desc) rn2 — earbasher, May 16 '23 at 17:12

score 0 · Answer 2 · answered May 16 '23 at 15:24

You can use UNION (to eleminate duplicates when same date occur in more than one select ) :

SELECT t.*
FROM mytable t
INNER JOIN (
  select refer, max(event_date) as recent_date
  from mytable 
  where refer = 2 
  group by refer
  union
  select refer, max(event_date) as recent_date
  from mytable where event_type = 'type 11' and cat1 = 'cat b'
  group by refer
  union
  select refer, max(event_date) as recent_date
  from mytable
  where cat1 = 'cat z'
  group by refer
) AS s on s.refer = t.refer and s.recent_date = t.event_date

Result :

refer   event_date  column_detail   event_type  cat1
2       2023-05-08  abc3            type 11     cat b
2       2023-05-16  abc123          type 4      cat a
2       2023-04-16  xyz             type 22     cat z

Demo here

in postgresql, can I combine this into a single query?

2 Answers2

Linked