Get the maximum of Country

Question

I have a table.

create table Company
( 
  CompanyID char(10),
  Country varchar(50),
  EstYear int
);

insert into Company values('A','USA',2016)
insert into Company values('A','USA',2017)
insert into Company values('A','UK',2018)
insert into Company values('A','USA',2019)
insert into Company values('A','Japan',2020)
insert into Company values('A','Japan',2021)
insert into Company values('A','USA',2022)
insert into Company values('B','USA',2016)
insert into Company values('B','UK',2017)
insert into Company values('B','UK',2018)
insert into Company values('B','Brazil',2019)
insert into Company values('B','Brazil',2020)
insert into Company values('B','Spain',2021)
insert into Company values('B','Brazil',2022)

I want to get this result.

CompanyID	Country
A	USA
B	Brazil

Thanks.

Answer 1

Hi @NNP

If I understand correctly, you can try this query.

;with CTE as (
  select CompanyId,Country,
    rank()over(partition by CompanyId order by Count(*) desc) as num
  from Company group by CompanyId,country)
select CompanyId,Country from CTE where num = 1;

Output:

Best regards,

Percy Tang

Answer 2

The next query gives your results, maybe accidentally:

select x.CompanyID, x.Country
from Company x
left join Company y on y.CompanyID = x.CompanyID and y.EstYear > x.EstYear
where y.CompanyID is null

Answer 3

Thanks all.