I am attempting to find the function in Cognos that lets me specify the rownumber over a given column. So the sql generated should look like the following
select row_number() over (PARTITION BY Field order by Field)
from table
What function can i use in Cognos to generate this SQL?
If the column is distinct , then go for "running-count"
Ex:running-count(cityname)
Quote from: srinivas_p on 10 Nov 2009 12:06:31 PM
If the column is distinct , then go for "running-count"
Ex:running-count(cityname)
Yes, this gives me what i needed. I also used the for clause so
running-count([table].[cityname] for [table].[state])
select row_number() over (PARTITION BY Field order by Field)
from table
cognos:
running-count(rank(orderByField for PartitionField) for PartitionField)