My sample source code is following
declare @t1 as table
(
site varchar(max),
service varchar(max),
yr integer,
mo integer
)
insert into @t1
select *
from
(values
('site1', 'service1', 2021, 1),
('site1', 'service1', 2021, 10),
('site1', 'service1', 2020, 12),
('site1', 'service1', 2019, 9),
('site1', 'service2', 2014, 5),
('site1', 'service2', 2015, 6),
('site1', 'service2', 2016, 7),
('site1', 'service2', 2016, 9),
('site2', 'service3', 2010, 2),
('site2', 'service3', 2011, 1),
('site2', 'service3', 2012, 3),
('site2', 'service3', 2012, 8) ) t (a, b, c, d)
I want to write a SQL query that would return a table grouped by site and service where it would first determine what is the max yr and then return the max of mo by previously determined max yr
My desired output is following
| site | service | maxYr | maxMo |
|-------|----------|-------|-------|
| site1 | service1 | 2021 | 10 |
| site1 | service2 | 2016 | 9 |
| site2 | service3 | 2012 | 8 |
Which I can presently achieve by following
select
a.site, a.service, a.yr as maxYr, max(a.mo) as maxMo
from
@t1 a
where
exists (select *
from
(select b.site, b.service, max(b.yr) as maxYr
from @t1 b
group by b.site, b.service) c
where a.site = c.site
and a.service = c.service
and a.yr = c.maxYr)
group by
a.site, a.service, a.yr
I was wondering if there is a better way to achieve this through a single query like
select
site, service, max(yr) as maxYr,
max(mo) over (partition by site, service order by max(yr)) as maxMo
from
@t1
group by
site, service
If I need to do further aggregation like Yr-Month-Date it would probably be easier for me to achieve through a single query.
You can use MAX() and FIRST_VALUE() window functions:
select distinct site, service,
max(yr) over (partition by site, service) as maxYr,
first_value(mo) over (partition by site, service order by yr desc, mo desc) as maxMo
from @t1;
See the demo.