WHILE 
		BEGIN

		DECLARE @temp_value_num int
		select @temp_value_num = 1;
		
		select --@rownum,
				temp_metric_id, metric_id, year, term_name, 
			   value, unit_id, currency_id, source, section, page, 
			   data_comment, @temp_value_num = value_number, risk_department_id, risk_area_id, 
			   period_id, institution_id
		from MTS_DATA_PRELOAD
		where institution_id = 1
		and metric_id = 666
		and CAST(term_name + ', ' + year as datetime) = '2009-12-01 00:00:00.000'
		and value_number = @value_num
		order by metric_id, institution_id, CAST(term_name + ', ' + year as datetime), value_number
		
		if @temp_value_num = @value_num
			@rownum +=1;
		else
			@value_num = @temp_value_num


		END
	END
[code]
As you can see, I have limited this to one specific unique combo of metric_id/institution_id/date.

I don't think this is right, since the select returns multiple rows, so I am not sure how I would operate on a row by row basis, and get an increasing row_number until the value_number changes. 

Any help appreciated!



[code]
USE [smts_temp_mdk]
GO
/****** Object:  Table [dbo].[MTS_DATA_PRELOAD]    Script Date: 02/22/2010 17:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTS_DATA_PRELOAD](
	[temp_metric_id] [numeric](5, 0) NULL,
	[metric_id] [numeric](5, 0) NULL,
	[year] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[term_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[temp_institution_short_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[institution_short_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[value] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[unit_id] [numeric](2, 0) NULL,
	[currency_id] [numeric](2, 0) NULL,
	[source] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[section] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[page] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[data_comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[value_number] [numeric](3, 0) NULL,
	[risk_department_id] [numeric](3, 0) NULL,
	[risk_area_id] [numeric](5, 0) NULL,
	[period_id] [numeric](7, 0) NULL,
	[institution_id] [numeric](10, 0) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [smts_temp_mdk].[dbo].[MTS_DATA_PRELOAD]
           ([temp_metric_id]
           ,[metric_id]
           ,[year]
           ,[term_name]
           ,[temp_institution_short_name]
           ,[institution_short_name]
           ,[value]
           ,[unit_id]
           ,[currency_id]
           ,[source]
           ,[section]
           ,[page]
           ,[data_comment]
           ,[value_number]
           ,[risk_department_id]
           ,[risk_area_id]
           ,[period_id]
           ,[institution_id])
     VALUES
           (<temp_metric_id, numeric,>
           ,<metric_id, numeric,>
           ,<year, varchar(4),>
           ,<term_name, varchar(30),>
           ,<temp_institution_short_name, varchar(50),>
           ,<institution_short_name, varchar(50),>
           ,<value, varchar(100),>
           ,<unit_id, numeric,>
           ,<currency_id, numeric,>
           ,<source, varchar(200),>
           ,<section, varchar(100),>
           ,<page, varchar(10),>
           ,<data_comment, varchar(2000),>
           ,<value_number, numeric,>
           ,<risk_department_id, numeric,>
           ,<risk_area_id, numeric,>
           ,<period_id, numeric,>
           ,<institution_id, numeric,>)

select row_number () over(partition by [value_number] order by [metric_id]) as rownum, *
from [dbo].[MTS_DATA_PRELOAD]

quote:

---

Originally posted by vijayisonly

Can you provide some sample data and expected output.

Nevertheless..Try this...Add WHERE conditions if appropriate.

select row_number () over(partition by [value_number] order by [metric_id]) as rownum, *
from [dbo].[MTS_DATA_PRELOAD]

---

select row_number () over(partition by dp.metric_id,dp.institution_id, dp.period_id,dp.[value_number] order by dp.[metric_id]) as rownum from MTS_DATA_PRELOAD