Retrieve rows in SQL based on Maximum value in multiple columns

Question

I have a SQL table with the following fields:

Company ID
Company Name
Fiscal Year
Fiscal Quarter

There are multiple records for various fiscal years and fiscal quarters for each company. I want to retrieve the rows for each company based on Maximum Fiscal Year and Maximum Fiscal Quarter. For example, if the table has the following:

Company ID  |  Company Name  |  Fiscal Year | Fiscal Quarter 
1           |  Test1         |  2017        | 1
1           |  Test1         |  2017        | 2
1           |  Test1         |  2018        | 1
1           |  Test1         |  2018        | 2
2           |  Test2         |  2018        | 3
2           |  Test2         |  2018        | 4

The query should return the following (Only the record with the maximum fiscal year and maximum fiscal quarter for that year):

Company ID  |  Company Name  |  Fiscal Year | Fiscal Quarter 
1           |  Test1         |  2018        | 2
2           |  Test2         |  2018        | 4

I am able to use the below query to get the records with the maximum fiscal year but not sure how to further select the maximum quarter within the year:

SELECT fp.companyId, fp.companyname, fp.fiscalyear,fp.fiscalquarter
FROM  dbo.ciqFinPeriod fp
  LEFT OUTER JOIN dbo.ciqFinPeriod fp2
    ON (fp.companyId = fp2.companyId AND fp.fiscalyear < fp2.fiscalyear)
WHERE fp2.companyId IS NULL

Thank you so much for any assistance!

Do you have the CompanyName is this table? That seems like a normalization issue to say the least. — Sean Lange
– Sean Lange, Commented May 29, 2018 at 15:38

Gordon Linoff · Accepted Answer · 2018-05-29 15:35:42Z

3

If you have a list of companies, I would simply do:

select fp.*
from Companies c outer apply
     (select top (1) fp.*
      from dbo.ciqFinPeriod fp
      where fp.companyId = c.companyId
      order by fp.fiscalyear desc, fp.fiscalquarter desc
     ) fp;

If not, then row_number() is probably the simplest method:

select fp.*
from (select fp.*,
             row_number() over (partition by fp.companyId order by order by fp.fiscalyear desc, fp.fiscalquarter desc) as seqnum
      from dbo.ciqFinPeriod fp
     ) fp
where seqnum = 1;

Or the somewhat more abstruse (clever ?):

select top (1) with ties fp.*
from dbo.ciqFinPeriod fp
order by row_number() over (partition by fp.companyId order by order by fp.fiscalyear desc, fp.fiscalquarter desc)

answered May 29, 2018 at 15:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Brandt Over a year ago

I'm a fan of the WITH TIES version. Abstruse and clever often walk hand in hand.

Amritha Over a year ago

Thanks so much! Both the second and third options work great and its also very efficient!

JonTout · Accepted Answer · 2018-05-29 16:12:25Z

0

I've had some success with the following, same output as you.

create table #table
(
CompanyID int,
CompanyName varchar(200),
Year int,
Quater int
)

insert into #table (CompanyID,CompanyName,Year,Quater)
VALUES

('1','Test1','2017','1'),
('1','Test1','2017','2'),
('1','Test1','2018','1'),
('1','Test1','2018','2'),
('2','Test2','2018','3'),
('2','Test2','2018','4')

SELECT CompanyID,CompanyName,Year,Quater  
FROM
(

Select CompanyID,CompanyName,Year,Quater
, ROW_NUMBER() OVER(PARTITION BY CompanyID ORDER BY Year desc,Quater DESC)         
as RowNum
from #table

) X  WHERE RowNum = 1

drop table #table

answered May 29, 2018 at 16:12

JonTout

6406 silver badges15 bronze badges

Comments

Hari · Accepted Answer · 2018-05-29 18:33:35Z

0

Select Company I'd, company name,Max(year),Max(quarter) group by 1,2

answered May 29, 2018 at 18:33

Hari

11 bronze badge

1 Comment

Hari Over a year ago

Is this you r expecting??

Collectives™ on Stack Overflow

Retrieve rows in SQL based on Maximum value in multiple columns

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related