Year and Month Max

Database
Enthusiast

Year and Month Max

Hi,

I have a table abc with data in the following format

Yr Mon Value
2006 2 100
2006 2 10
2006 4 30
2006 5 15
2007 1 20
2007 1 15
2007 2 30
2007 2 45

Yr and Mon are integer fields

I want to select data from abc where the yr = max(yr) from abc and month = max(mon) from abc

In the above case the max yr mon = 2007 2. So my results should be
2007 2 30
2007 2 45

Whats the best way to do it
3 REPLIES
Enthusiast

Re: Year and Month Max

select yr
,mon
,value
from abc
where (yr (format '9999') || mo (format '99')) in
(select max(yr (format '9999') || mo (format '99'))
from abc);
Junior Contributor

Re: Year and Month Max

Hi Sam,
select *
from tab
qualify
rank() over (order by yr desc, mon desc) = 1

Dieter
Enthusiast

Re: Year and Month Max

Thanks Barry and Dieter
Sam