December 17, 2015 at 2:02 pm
Hi there,
I have a table with company info and I was trying to pick shorter period for each asofdate and total number of records per company shouldn't exceed 8.
create table temp(Companyid varchar(10),asofdate datetime,period varchar(10));
insert into temp values('0SN','2013-09-30 00:00:00','3M')
insert into temp values('0SN','2013-09-30 00:00:00','9M')
insert into temp values('0SN','2013-06-30 00:00:00','3M')
insert into temp values('0SN','2013-06-30 00:00:00','6M')
insert into temp values('0SN','2013-03-31 00:00:00','3M')
insert into temp values('0SN','2012-09-30 00:00:00','3M')
insert into temp values('0SN','2012-09-30 00:00:00','9M')
insert into temp values('0SN','2012-06-30 00:00:00','3M')
insert into temp values('0SN','2012-06-30 00:00:00','6M')
insert into temp values('0SN','2010-03-31 00:00:00','3M')
insert into temp values('0SN','2010-09-30 00:00:00','3M')
insert into temp values('0SN','2010-09-30 00:00:00','9M')
insert into temp values('0SN','2010-06-30 00:00:00','3M')
insert into temp values('0SN','2010-06-30 00:00:00','6M')
insert into temp values('0SN','2010-03-31 00:00:00','3M')
I want to get
Companyid,asofdate,period,rank
'0SN','2013-09-30 00:00:00','3M', 1
'0SN','2013-09-30 00:00:00','9M', 2
'0SN','2013-06-30 00:00:00','3M',1
'0SN','2013-06-30 00:00:00','6M',2
'0SN','2013-03-31 00:00:00','3M',1
'0SN','2012-09-30 00:00:00','3M', 1
'0SN','2012-09-30 00:00:00','9M', 2
'0SN','2012-06-30 00:00:00','3M',1
'0SN','2012-06-30 00:00:00','6M',2
'0SN','2012-03-31 00:00:00','3M',1
'0SN','2010-09-30 00:00:00','3M', 1
'0SN','2010-09-30 00:00:00','9M', 2
'0SN','2010-06-30 00:00:00','3M',1
'0SN','2010-06-30 00:00:00','6M',2
'0SN','2010-03-31 00:00:00','3M',1
and then print records whose rank=1 and print only 8 such records i.e.
'0SN','2013-09-30 00:00:00','3M', 1
'0SN','2013-06-30 00:00:00','3M',1
'0SN','2013-03-31 00:00:00','3M',1
'0SN','2012-09-30 00:00:00','3M', 1
'0SN','2012-06-30 00:00:00','3M',1
'0SN','2012-03-31 00:00:00','3M',1
'0SN','2010-09-30 00:00:00','3M', 1
'0SN','2010-06-30 00:00:00','3M',1
How can I do that?
thanks
Rash
December 17, 2015 at 2:20 pm
Does this do what you need?
with cte as (
select
Companyid
,asofdate
,period
,row_number() over(partition by Companyid, asofdate order by period) as rownum
from temp)
select *
from cte
where rownum = 1
December 17, 2015 at 2:26 pm
Sorry just noticed the requirement for a maximum of 8 rows per company
with cte as (
select
Companyid
,asofdate
,period
,row_number() over(partition by Companyid, asofdate order by period) as rownum
from temp)
select *
from (select Companyid
,asofdate
,period
,rownum
,row_number() over(partition by Companyid order by CompanyID) rownum2
from cte
where rownum = 1) as x
where rownum2 <= 8
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply