insert into activity(activity_id,general_id,customer_id,fy,value1)values(1,1,1,2009,10);insert into activity(activity_id,general_id,customer_id,fy,value1)values(2,1,1,2010,20);insert into activity(activity_id,general_id,customer_id,fy,value1)values(3,1,1,2011,30);insert into activity(activity_id,general_id,customer_id,fy,value1)values(4,1,1,2012,40);

insert into activity(activity_id,general_id,customer_id,fy,value1)values(5,1,2,2009,50);insert into activity(activity_id,general_id,customer_id,fy,value1)values(6,1,2,2010,60);insert into activity(activity_id,general_id,customer_id,fy,value1)values(7,1,2,2011,70);insert into activity(activity_id,general_id,customer_id,fy,value1)values(8,1,2,2012,80);

insert into activity(activity_id,general_id,customer_id,fy,value1)values(9,2,1,2011,90);insert into activity(activity_id,general_id,customer_id,fy,value1)values(10,2,1,2012,100);insert into activity(activity_id,general_id,customer_id,fy,value1)values(11,2,1,2013,110);insert into activity(activity_id,general_id,customer_id,fy,value1)values(12,2,1,2014,120);insert into activity(activity_id,general_id,customer_id,fy,value1)values(13,2,1,2015,130);

Here is my select statement

select fy, value1, dense_rank() over (partition by general_id order by fy ) rnk from activity where general_id = 2 and fy between 2008 and 2016

I am pivoting the values but how can i display zero for the missing year 2008, 2009... ?

with years(yr) as (select 2008 from dual
UNION ALL
select yr+1 from years
where yr < 2016)
select fy, value1,
dense_rank() over (partition by a.general_id order by y.yr ) rnk
from years y
left join activity a on a.fy = y.yr and general_id = 2

with years as (
select 2008 + level - 1 as yr from dual
connect by level <= 8
)
select y.yr, nvl(a.value1,0) as value1,
dense_rank() over (partition by a.general_id order by y.yr ) rnk
from years y
left join activity a on a.fy = y.yr and a.general_id = 1 and a.customer_id = 1
order by y.yr;

Sorry, I don't know what you want. I'm guessing that the results you posted are not what you want, but what are the right results? Post them, and explain how you get them from the sample data you posted in your first mesage. (Or post some different sample data if you need to.)

In any analytic function, such as DENSE_RANK, PARTITION BY x" means that each value of x is a world unto itself. It's as if you did a separate query, with a separate computation of the analytic function, for each value of x, and then UNIONed all the results together. Since you're saying "PARTITION BY a.general_id", then DENSE_RANK will assign a different set of values (1, 2, 3, ...) to each distinct value of a.general_id.

The only result that is not correct is the RNK column, it should be in order from 1 to 8. This happened after I included the a.customer_id column.

When I removed the partition clause from the dense_rank as "dense_rank() over ( order by y.yr ) rnk", it works... why?

Thanks !

As I said in the last message, "PARTITION BY a.general_id" is asking for a separate computation for each distinct value of a.general_id. In this case, a.general_id has 2 distinct values, 1 and 2, so DENSE_RANK looks only at the rows with a.general_id=1 and assigns one set of results (1, 2, 3 and 4) to those rows, then it looks only at the rows with a.general_id=2 and assigns another set of results (1, 2, 3 and 4 again).

The PARTITION BY clause is always optional; that is, any analytic function can work with or without a PARTITION BY clause.. If you want all rows in the result set to influence the function, then omit the PARTITION BY clause.