ROW_NUMBER() and possibly group by

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  September 22, 2008 at 10:47 am

  #194034

  Hi,

  i've got a dataset that resembles the following:

  Name Dept Seq

  Mary DeptA NULL

  Mary DeptB NULL

  Mary DeptC NULL

  John DeptA NULL

  John DeptB NULL

  I'd like to produce the following:

  Name Dept Seq

  Mary DeptA 1

  Mary DeptB 2

  Mary DeptC 3

  John DeptA 1

  John DeptB 2

  where the row_number() function is effectively grouped by the Name column.

  I've tried various ways including a recursive CTE expression based on a grouped bt rowcount value but this produces more of a cross-join effect ......I just can't get this correct.

  I basically need to populate an incemental sequence value against each row with a common Name.

  Any ideas out there?

  Any help welcome.

  Eamon:)

  ps.......i'd like to avoid a cursor solution if possible !

• Mohit Nayyar

  Ten Centuries

  Points: 1008

  More actions

  September 22, 2008 at 11:04 am

  #875160

  SELECT

  ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY dept, name) AS Seq

  , *

  FROM TableName

  Thanks
  Mohit Nayyar
  http://mohitnayyar.blogspot.com/
  "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  September 23, 2008 at 12:53 am

  #875438

  Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

  ---

  Madhivanan

  Failing to plan is Planning to fail

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  September 23, 2008 at 8:34 am

  #875715

  Perfect !!

  THANKS 😀