TSQL to max multiple columns in sequence

Question

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.

Imagine you had a single value that represented both year and month (like a date). Then you can use a single and simple MAX aggregate to get both values. So you need a date (day part irrelevant if we make assumptions - do you see yours?). Now how can you generate a date from parts ? — SMor, Aug 19 '21 at 17:20
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Aug 19 '21 at 19:28

score 2 · Answer 1 · answered Aug 19 '21 at 17:45

2

Just as an alternative given you have an answer, you could also just use the

ROW_NUMBER function and order by SITE and SERVICE descending:

SELECT SITE,SERVICE,YR,MO 
FROM
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY SITE, SERVICE ORDER BY YR DESC, MO DESC ) RN
    FROM @t1
) X
WHERE X.RN = 1

answered Aug 19 '21 at 17:45

JMabee

2,230
2
9
13

It has lesser subtree cost. – smpa01 Aug 19 '21 at 17:54

score 0 · Accepted Answer · answered Aug 19 '21 at 17:17

0

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.

answered Aug 19 '21 at 17:17

forpas

160,666
10
38
76

1

tested out with further two levels after Yr, Month and `FIRST_VALUE()` does awesome drill through by each preceding max level. thanks !!! – smpa01 Aug 19 '21 at 17:45

TSQL to max multiple columns in sequence

2 Answers2