Question 169658 in PostgreSQL Question posted in
The official documentation can be found here.

Can I combine this into a single query in Postgresql?

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'
);
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search