Author Topic: Rownumber()  (Read 19551 times)

Offline cognos_guy_

  • Community Leader
  • *****
  • Join Date: Mar 2009
  • Posts: 104
  • Forum Citizenship: +2/-0
Rownumber()
« on: 09 Nov 2009 02:47:59 pm »
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

Code: [Select]
select row_number() over (PARTITION BY Field order by Field)
from table

What function can i use in Cognos to generate this SQL?

Offline Sreeni P

  • Community Leader
  • *****
  • Join Date: Jun 2009
  • Posts: 123
  • Forum Citizenship: +1/-3
  • Coming through !...
Re: Rownumber()
« Reply #1 on: 10 Nov 2009 12:06:31 pm »
If the column is distinct , then go for "running-count"
Ex:running-count(cityname)

Offline cognos_guy_

  • Community Leader
  • *****
  • Join Date: Mar 2009
  • Posts: 104
  • Forum Citizenship: +2/-0
Re: Rownumber()
« Reply #2 on: 12 Nov 2009 08:55:12 am »
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

Code: [Select]
running-count([table].[cityname] for [table].[state])

Offline alik

  • Associate
  • **
  • Join Date: Nov 2020
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: Rownumber()
« Reply #3 on: 03 Nov 2020 03:24:37 am »
select row_number() over (PARTITION BY Field order by Field)
from table

cognos:
running-count(rank(orderByField for PartitionField) for PartitionField)