Unque numbering rows in SQl Server for duplicate values

Question

Is it possible to re-write this query to restart numbering when the UName changes so that my result set is:

1 FerrieC 2 FerrieC 3 FerrieC 1 GrayD 1 TimneyA 2 TimneyA

SELECT Row_Number() OVER (ORDER BY u.UName) as RowNumber , u.UName FROM ( SELECT 'Ferriec' As UName UNION ALL SELECT 'Ferriec' As UName UNION ALL SELECT 'Ferriec' As UName UNION ALL SELECT 'TimneyA' As UName UNION ALL SELECT 'TimneyA' As UName UNION ALL SELECT 'GrayD' As UName ) as u

Thansk! :)

Answer 1

Sure:

ROW_NUMBER OVER (ORDER BY u.UName) - RANK OVER (ORDER BY u.UName) + 1

http://msdn.microsoft.com/en-us/library/ms189798.aspx

Answer 2

Try this:

SELECT   Row_Number() OVER (PARTITION BY UName ORDER BY u.UName) as RowNumber 
         ,u.UName 
FROM  (  SELECT 'Ferriec' As UName UNION ALL 
         SELECT 'Ferriec' As UName UNION ALL 
         SELECT 'Ferriec' As UName UNION ALL 
         SELECT 'TimneyA' As UName UNION ALL 
         SELECT 'TimneyA' As UName UNION ALL 
         SELECT 'GrayD' As UName ) as u