number grouped sets ( SQL 2005)

• touchmeknot123

  Right there with Babe

  Points: 735

  More actions

  January 9, 2010 at 1:00 pm

  #137193

  Hi,

  I have a table as following

  SSN Amount

  1 101

  1 210

  1 120

  2 220

  2 150

  2 300

  Is there a way that I can have them grouped by SSN and number them starting from 1 to 3.

  The number of records in a group are fixed. (like in above case, it is 3).

  The following table is required

  row_num SSN Amount

  1 1 101

  2 1 210

  3 1 120

  1 2 220

  2 2 150

  3 2 300

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  January 9, 2010 at 4:35 pm

  #1101442

  SELECT ROW_NUMBER() OVER PARTITON BY SSN ORDER BY Amount) AS row_num,

  SSN, Amount

  FROM Table1

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• touchmeknot123

  Right there with Babe

  Points: 735

  More actions

  January 9, 2010 at 6:41 pm

  #1101448

  Thanks SwePeso!