Sequence

• Oleg Netchaev

  SSCertifiable

  Points: 5276

  More actions

  December 28, 2010 at 1:35 pm

  #1266913

  SQLkiwi (12/28/2010)

  ---

  Or, even more succinctly:

  WITH Records (Old_Seq, New_Seq)

  AS (

  SELECT Seq,

  ROW_NUMBER() OVER (PARTITION BY Value ORDER BY RecID)

  FROM #Test

  )

  UPDATE Records

  SET Old_Seq = New_Seq;

  Paul

  This is really elegant, thank you so much Paul! Since there is no way to use the windowing functions directly in the set, I thought that update from join is a necessary evil to workaround the issue. I am glad that it is not.

  Oleg

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  April 29, 2011 at 9:59 am

  #1317849

  get all those numbers in one row

  Now I understand what you were trying to achieve. Thanks for the question.