skip to Main Content

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

2

Answers


  1. 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. 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