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
We can enumerate the three partitions with three
row_number
s, then pivot with conditional aggregation: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:
You can use
UNION
(to eleminate duplicates when same date occur in more than one select ) :Result :
Demo here