Hello, I am interested in creating a new field where row number is user controlled depending on two varchar fields. Input: ID Name x1 xyz x1 xyz x1 xyz y1 ab y1 ab s1 gb s1 fg Output I want is ID Name row number x1 xyz 1 x1 xyz 2 x1 xyz 3 y1 ab 1 y1 ab 2 s1 gb 1 s1 fg 1 How do I proceed with this? Thanks gs

You can use ROW_NUMBER to create the desired row_number value: SELECT ID ,Name ,ROW_NUMBER() OVER(PARTITION BY ID, Name ORDER BY ID, Name) AS [row number] FROM dbo.MyTable ORDER BY ID ,Name Note, however, that the ordering of the results may differ from the order in the output you requested. A table is an unordered set of rows and I don't see a value in the source data that will provide that sequence. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

if your source is sql server 2005 or higher, then you can use Dan's suggestion. But if you have any other sources, you can create a row number with script component, this is a sample: http://support.microsoft.com/kb/908460http://www.rad.pasfu.com My Submitted sessions at sqlbits.com