Updating a Sequence Number with Order

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  December 21, 2013 at 7:31 pm

  #301633

  I need to update a sequence number that is used to display a list. The order is not crucial except when the VisualRIN > 0. These I would like at the top, then everything else.

  Table Def

  declare @t1 table

  (

  [RIN] [int] IDENTITY(1,1) NOT NULL,

  [Year] [int] NOT NULL,

  [TRMasterRIN] [int] NOT NULL,

  [TRValueRIN] [int] NOT NULL,

  [AttributeRIN] [int] NOT NULL,

  [QualityRIN] [int] NOT NULL,

  [VisualRIN] [int] NOT NULL,

  [ErrStringRIN] [int] NOT NULL,

  [Sequence] [int] NOT NULL,

  [Arg1] [real] NOT NULL,

  [Arg2] [real] NOT NULL,

  [Arg3] [real] NOT NULL,

  [Arg4] [real] NOT NULL,

  [Arg5] [real] NOT NULL,

  [Amount] [int] NOT NULL

  )

  Data

  INSERT @t1 (Year, TRMasterRIN, TRValueRIN, AttributeRIN, QualityRIN, VisualRIN, ErrStringRIN, sequence, Arg1, Arg2, Arg3, Arg4, Arg5, Amount) VALUES

  (2014,526627,872055,500,0,0,0,1,440,100,0,0,0,5),

  (2014,526627,872055,1957,-2,53568,0,2,1,0,0,0,0,0),

  (2014,526627,872055,253,0,0,0,3,800,0,0,0,0,1),

  (2014,526627,872055,2,0,0,0,4,0,100,0,0,0,1),

  (2014,526627,872055,36,0,0,0,5,1968,100,0,0,0,4),

  (2014,526627,872055,200,0,0,0,6,1968,0,0,0,0,9),

  (2014,526627,872055,136,0,0,0,7,0,0,0,0,0,0),

  (2014,526627,872055,197,0,0,0,8,9,0,0,0,0,2),

  (2014,526627,872055,122,0,0,0,9,1968,0,0,0,0,8),

  (2014,526627,872055,403,0,0,0,10,576,100,0,0,0,1),

  (2014,526627,872055,1953,-2,26529,0,11,1,0,1,0,0,0),

  (2014,526627,872055,1953,-2,28152,0,12,0,0,0,0,0,0),

  (2014,526627,872055,1954,-2,11628,0,13,0,1,0,0,0,1),

  (2014,526627,872055,529,0,0,0,14,240,100,0,0,0,7),

  (2014,526627,872055,1246,0,0,0,15,240,0,0,0,0,0),

  (2014,526627,872055,1255,3,0,0,16,3000,0,0,0,0,0),

  (2014,526627,872055,162,2,0,0,17,1,0,0,0,0,7),

  (2014,526627,872055,1264,2,0,0,18,48,100,0,0,0,7),

  (2014,526627,872055,1960,-2,0,0,19,9862,0,0,0,0,0),

  (2014,526627,872055,1961,-2,0,0,20,169395,0,0,0,0,1),

  (2014,526627,872055,1874,-2,0,0,21,169395,0,0,0,2,0),

  (2014,526627,872055,1947,-2,0,0,22,206661,0,0,0,4,0),

  (2014,526627,872055,1863,-2,0,0,23,299658,74801,42,68,48,-3),

  (2014,526627,872055,1962,-2,0,0,24,95890,0,0,0,0,-1)

  This is the code that I have been using to do the resequencing.

  DECLARE @iseq int = 0,

  @iLastVal int = 0

  UPDATE A SET

  Sequence = @iseq,

  @iseq = CASE

  WHEN @iLastVal = A.TRValueRIN THEN @iseq + 1

  ELSE 1 END,

  @iLastVal = A.TRValueRIN

  FROM @t1 A

  JOIN (SELECT TOP 100 PERCENT Year, TRMasterRIN, TRValueRIN, VisualRIN

  FROM @t1

  WHERE Year = 2014 and TRMasterRIN = 526627 and TRValueRIN = 872055

  --ORDER BY VisualRIN desc --<<--- does not help

  ) A1 ON A1.Year = A.Year and A1.TRMasterRIN = A.TRMasterRIN and A1.TRValueRIN = A.TRValueRIN

  Any ideas are appreciated!

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  December 22, 2013 at 5:33 am

  #1675446

  Can you please provide us some more information...

  Expected output from given input data?

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  December 22, 2013 at 1:02 pm

  #1675464

  LinksUp (12/21/2013)

  ---

  I need to update a sequence number that is used to display a list. The order is not crucial except when the VisualRIN > 0. These I would like at the top, then everything else.

  First, nice job on providing some readily consumable data for us to test with. I'm surprised that no one jumped on this earlier because it's all there.

  I do have a suggestion, though. Even though this is a 2008 forum, there are a lot of us (30 to 50% of us) that are still "stuck" with 2005 on the machines that we have to work on. Since most problems don't require any functionality that's not available in 2005 or up, I recommend that, in the future, you make the test data using "old fashioned" UNION ALL's instead of VALUES to allow more people the opportunity to help you. Like this...

  declare @t1 table

  (

  [RIN] [int] IDENTITY(1,1) NOT NULL,

  [Year] [int] NOT NULL,

  [TRMasterRIN] [int] NOT NULL,

  [TRValueRIN] [int] NOT NULL,

  [AttributeRIN] [int] NOT NULL,

  [QualityRIN] [int] NOT NULL,

  [VisualRIN] [int] NOT NULL,

  [ErrStringRIN] [int] NOT NULL,

  [Sequence] [int] NOT NULL,

  [Arg1] [real] NOT NULL,

  [Arg2] [real] NOT NULL,

  [Arg3] [real] NOT NULL,

  [Arg4] [real] NOT NULL,

  [Arg5] [real] NOT NULL,

  [Amount] [int] NOT NULL

  )

  ;

  INSERT @t1

  (Year, TRMasterRIN, TRValueRIN, AttributeRIN, QualityRIN, VisualRIN, ErrStringRIN, sequence, Arg1, Arg2, Arg3, Arg4, Arg5, Amount)

  SELECT 2014,526627,872055,500,0,0,0,1,440,100,0,0,0,5 UNION ALL

  SELECT 2014,526627,872055,1957,-2,53568,0,2,1,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,253,0,0,0,3,800,0,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,2,0,0,0,4,0,100,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,36,0,0,0,5,1968,100,0,0,0,4 UNION ALL

  SELECT 2014,526627,872055,200,0,0,0,6,1968,0,0,0,0,9 UNION ALL

  SELECT 2014,526627,872055,136,0,0,0,7,0,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,197,0,0,0,8,9,0,0,0,0,2 UNION ALL

  SELECT 2014,526627,872055,122,0,0,0,9,1968,0,0,0,0,8 UNION ALL

  SELECT 2014,526627,872055,403,0,0,0,10,576,100,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,1953,-2,26529,0,11,1,0,1,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1953,-2,28152,0,12,0,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1954,-2,11628,0,13,0,1,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,529,0,0,0,14,240,100,0,0,0,7 UNION ALL

  SELECT 2014,526627,872055,1246,0,0,0,15,240,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1255,3,0,0,16,3000,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,162,2,0,0,17,1,0,0,0,0,7 UNION ALL

  SELECT 2014,526627,872055,1264,2,0,0,18,48,100,0,0,0,7 UNION ALL

  SELECT 2014,526627,872055,1960,-2,0,0,19,9862,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1961,-2,0,0,20,169395,0,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,1874,-2,0,0,21,169395,0,0,0,2,0 UNION ALL

  SELECT 2014,526627,872055,1947,-2,0,0,22,206661,0,0,0,4,0 UNION ALL

  SELECT 2014,526627,872055,1863,-2,0,0,23,299658,74801,42,68,48,-3 UNION ALL

  SELECT 2014,526627,872055,1962,-2,0,0,24,95890,0,0,0,0,-1

  ;

  On to your problem...

  Instead of just solving this for a given Year, TRMasterRIN, and TRValueRIN combination, let's solve it for all unique combinations of those 3 columns by using the PARTITION clause of ROW_NUMBER(). We'll also get rid of the complicated joins by understanding that, like certain Views are, CTEs can be updateable. That also helps us get rid of the controversial "Quirky Update" method that you're using and makes the code one hell of a lot simpler and faster. Note that I added several more groups of data (based on the original data) to prove that the sequence works correctly against unique combinations of Year, TRMasterRIN, and TRValueRIN.

  declare @t1 table

  (

  [RIN] [int] IDENTITY(1,1) NOT NULL,

  [Year] [int] NOT NULL,

  [TRMasterRIN] [int] NOT NULL,

  [TRValueRIN] [int] NOT NULL,

  [AttributeRIN] [int] NOT NULL,

  [QualityRIN] [int] NOT NULL,

  [VisualRIN] [int] NOT NULL,

  [ErrStringRIN] [int] NOT NULL,

  [Sequence] [int] NOT NULL,

  [Arg1] [real] NOT NULL,

  [Arg2] [real] NOT NULL,

  [Arg3] [real] NOT NULL,

  [Arg4] [real] NOT NULL,

  [Arg5] [real] NOT NULL,

  [Amount] [int] NOT NULL

  )

  ;

  INSERT @t1

  (Year, TRMasterRIN, TRValueRIN, AttributeRIN, QualityRIN, VisualRIN, ErrStringRIN, sequence, Arg1, Arg2, Arg3, Arg4, Arg5, Amount)

  SELECT 2014,526627,872055,500,0,0,0,1,440,100,0,0,0,5 UNION ALL

  SELECT 2014,526627,872055,1957,-2,53568,0,2,1,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,253,0,0,0,3,800,0,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,2,0,0,0,4,0,100,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,36,0,0,0,5,1968,100,0,0,0,4 UNION ALL

  SELECT 2014,526627,872055,200,0,0,0,6,1968,0,0,0,0,9 UNION ALL

  SELECT 2014,526627,872055,136,0,0,0,7,0,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,197,0,0,0,8,9,0,0,0,0,2 UNION ALL

  SELECT 2014,526627,872055,122,0,0,0,9,1968,0,0,0,0,8 UNION ALL

  SELECT 2014,526627,872055,403,0,0,0,10,576,100,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,1953,-2,26529,0,11,1,0,1,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1953,-2,28152,0,12,0,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1954,-2,11628,0,13,0,1,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,529,0,0,0,14,240,100,0,0,0,7 UNION ALL

  SELECT 2014,526627,872055,1246,0,0,0,15,240,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1255,3,0,0,16,3000,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,162,2,0,0,17,1,0,0,0,0,7 UNION ALL

  SELECT 2014,526627,872055,1264,2,0,0,18,48,100,0,0,0,7 UNION ALL

  SELECT 2014,526627,872055,1960,-2,0,0,19,9862,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,872055,1961,-2,0,0,20,169395,0,0,0,0,1 UNION ALL

  SELECT 2014,526627,872055,1874,-2,0,0,21,169395,0,0,0,2,0 UNION ALL

  SELECT 2014,526627,872055,1947,-2,0,0,22,206661,0,0,0,4,0 UNION ALL

  SELECT 2014,526627,872055,1863,-2,0,0,23,299658,74801,42,68,48,-3 UNION ALL

  SELECT 2014,526627,872055,1962,-2,0,0,24,95890,0,0,0,0,-1 UNION ALL

  --===== Year changed

  SELECT 2013,526627,872055,500,0,0,0,1,440,100,0,0,0,5 UNION ALL

  SELECT 2013,526627,872055,1957,-2,53568,0,2,1,0,0,0,0,0 UNION ALL

  SELECT 2013,526627,872055,253,0,0,0,3,800,0,0,0,0,1 UNION ALL

  SELECT 2013,526627,872055,2,0,0,0,4,0,100,0,0,0,1 UNION ALL

  SELECT 2013,526627,872055,36,0,0,0,5,1968,100,0,0,0,4 UNION ALL

  SELECT 2013,526627,872055,200,0,0,0,6,1968,0,0,0,0,9 UNION ALL

  SELECT 2013,526627,872055,136,0,0,0,7,0,0,0,0,0,0 UNION ALL

  SELECT 2013,526627,872055,197,0,0,0,8,9,0,0,0,0,2 UNION ALL

  SELECT 2013,526627,872055,122,0,0,0,9,1968,0,0,0,0,8 UNION ALL

  SELECT 2013,526627,872055,403,0,0,0,10,576,100,0,0,0,1 UNION ALL

  SELECT 2013,526627,872055,1953,-2,26529,0,11,1,0,1,0,0,0 UNION ALL

  SELECT 2013,526627,872055,1953,-2,28152,0,12,0,0,0,0,0,0 UNION ALL

  SELECT 2013,526627,872055,1954,-2,11628,0,13,0,1,0,0,0,1 UNION ALL

  SELECT 2013,526627,872055,529,0,0,0,14,240,100,0,0,0,7 UNION ALL

  SELECT 2013,526627,872055,1246,0,0,0,15,240,0,0,0,0,0 UNION ALL

  SELECT 2013,526627,872055,1255,3,0,0,16,3000,0,0,0,0,0 UNION ALL

  SELECT 2013,526627,872055,162,2,0,0,17,1,0,0,0,0,7 UNION ALL

  SELECT 2013,526627,872055,1264,2,0,0,18,48,100,0,0,0,7 UNION ALL

  SELECT 2013,526627,872055,1960,-2,0,0,19,9862,0,0,0,0,0 UNION ALL

  SELECT 2013,526627,872055,1961,-2,0,0,20,169395,0,0,0,0,1 UNION ALL

  SELECT 2013,526627,872055,1874,-2,0,0,21,169395,0,0,0,2,0 UNION ALL

  SELECT 2013,526627,872055,1947,-2,0,0,22,206661,0,0,0,4,0 UNION ALL

  SELECT 2013,526627,872055,1863,-2,0,0,23,299658,74801,42,68,48,-3 UNION ALL

  SELECT 2013,526627,872055,1962,-2,0,0,24,95890,0,0,0,0,-1 UNION ALL

  --===== TRValueRin changed

  SELECT 2014,526627,123456,500,0,0,0,1,440,100,0,0,0,5 UNION ALL

  SELECT 2014,526627,123456,1957,-2,53568,0,2,1,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,123456,253,0,0,0,3,800,0,0,0,0,1 UNION ALL

  SELECT 2014,526627,123456,2,0,0,0,4,0,100,0,0,0,1 UNION ALL

  SELECT 2014,526627,123456,36,0,0,0,5,1968,100,0,0,0,4 UNION ALL

  SELECT 2014,526627,123456,200,0,0,0,6,1968,0,0,0,0,9 UNION ALL

  SELECT 2014,526627,123456,136,0,0,0,7,0,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,123456,197,0,0,0,8,9,0,0,0,0,2 UNION ALL

  SELECT 2014,526627,123456,122,0,0,0,9,1968,0,0,0,0,8 UNION ALL

  SELECT 2014,526627,123456,403,0,0,0,10,576,100,0,0,0,1 UNION ALL

  SELECT 2014,526627,123456,1953,-2,26529,0,11,1,0,1,0,0,0 UNION ALL

  SELECT 2014,526627,123456,1953,-2,28152,0,12,0,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,123456,1954,-2,11628,0,13,0,1,0,0,0,1 UNION ALL

  SELECT 2014,526627,123456,529,0,0,0,14,240,100,0,0,0,7 UNION ALL

  SELECT 2014,526627,123456,1246,0,0,0,15,240,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,123456,1255,3,0,0,16,3000,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,123456,162,2,0,0,17,1,0,0,0,0,7 UNION ALL

  SELECT 2014,526627,123456,1264,2,0,0,18,48,100,0,0,0,7 UNION ALL

  SELECT 2014,526627,123456,1960,-2,0,0,19,9862,0,0,0,0,0 UNION ALL

  SELECT 2014,526627,123456,1961,-2,0,0,20,169395,0,0,0,0,1 UNION ALL

  SELECT 2014,526627,123456,1874,-2,0,0,21,169395,0,0,0,2,0 UNION ALL

  SELECT 2014,526627,123456,1947,-2,0,0,22,206661,0,0,0,4,0 UNION ALL

  SELECT 2014,526627,123456,1863,-2,0,0,23,299658,74801,42,68,48,-3 UNION ALL

  SELECT 2014,526627,123456,1962,-2,0,0,24,95890,0,0,0,0,-1 UNION ALL

  --===== TRMasterRIN changed

  SELECT 2014,654321,123456,500,0,0,0,1,440,100,0,0,0,5 UNION ALL

  SELECT 2014,654321,123456,1957,-2,53568,0,2,1,0,0,0,0,0 UNION ALL

  SELECT 2014,654321,123456,253,0,0,0,3,800,0,0,0,0,1 UNION ALL

  SELECT 2014,654321,123456,2,0,0,0,4,0,100,0,0,0,1 UNION ALL

  SELECT 2014,654321,123456,36,0,0,0,5,1968,100,0,0,0,4 UNION ALL

  SELECT 2014,654321,123456,200,0,0,0,6,1968,0,0,0,0,9 UNION ALL

  SELECT 2014,654321,123456,136,0,0,0,7,0,0,0,0,0,0 UNION ALL

  SELECT 2014,654321,123456,197,0,0,0,8,9,0,0,0,0,2 UNION ALL

  SELECT 2014,654321,123456,122,0,0,0,9,1968,0,0,0,0,8 UNION ALL

  SELECT 2014,654321,123456,403,0,0,0,10,576,100,0,0,0,1 UNION ALL

  SELECT 2014,654321,123456,1953,-2,26529,0,11,1,0,1,0,0,0 UNION ALL

  SELECT 2014,654321,123456,1953,-2,28152,0,12,0,0,0,0,0,0 UNION ALL

  SELECT 2014,654321,123456,1954,-2,11628,0,13,0,1,0,0,0,1 UNION ALL

  SELECT 2014,654321,123456,529,0,0,0,14,240,100,0,0,0,7 UNION ALL

  SELECT 2014,654321,123456,1246,0,0,0,15,240,0,0,0,0,0 UNION ALL

  SELECT 2014,654321,123456,1255,3,0,0,16,3000,0,0,0,0,0 UNION ALL

  SELECT 2014,654321,123456,162,2,0,0,17,1,0,0,0,0,7 UNION ALL

  SELECT 2014,654321,123456,1264,2,0,0,18,48,100,0,0,0,7 UNION ALL

  SELECT 2014,654321,123456,1960,-2,0,0,19,9862,0,0,0,0,0 UNION ALL

  SELECT 2014,654321,123456,1961,-2,0,0,20,169395,0,0,0,0,1 UNION ALL

  SELECT 2014,654321,123456,1874,-2,0,0,21,169395,0,0,0,2,0 UNION ALL

  SELECT 2014,654321,123456,1947,-2,0,0,22,206661,0,0,0,4,0 UNION ALL

  SELECT 2014,654321,123456,1863,-2,0,0,23,299658,74801,42,68,48,-3 UNION ALL

  SELECT 2014,654321,123456,1962,-2,0,0,24,95890,0,0,0,0,-1

  ;

  WITH

  cte AS

  ( --=== Calculate the new sequence and expose the column to be updated

  SELECT [Sequence]

  ,NewSequence =

  ROW_NUMBER() OVER

  (

  PARTITION BY Year, TRMasterRIN, TRValueRIN

  ORDER BY CASE WHEN VisualRIN > 0 THEN VisualRIN ELSE 2147483647 END, RIN

  )

  FROM @t1

  ) --=== Now, simply update the exposed column from the calculated column

  -- This actually does update the underlying table in the CTE.

  UPDATE cte

  SET [Sequence] = NewSequence

  ;

  --===== Display the results in the expected order

  SELECT *

  FROM @t1

  ORDER BY Year, TRMasterRIN, TRValueRIN, Sequence

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  December 23, 2013 at 10:02 am

  #1675646

  Jeff Moden (12/22/2013)

  ---

  First, nice job on providing some readily consumable data for us to test with. I'm surprised that no one jumped on this earlier because it's all there.

  Thanks!

  I do have a suggestion, though. Even though this is a 2008 forum, there are a lot of us (30 to 50% of us) that are still "stuck" with 2005 on the machines that we have to work on. Since most problems don't require any functionality that's not available in 2005 or up, I recommend that, in the future, you make the test data using "old fashioned" UNION ALL's instead of VALUES to allow more people the opportunity to help you.

  I will keep that in mind next time I post. My first exposure to SQL was with 2008 R2 so I had never even considered using UNION ALL!

  What an incredible solution! It is SO much faster than what I had been using previously. After studying it for a bit, it makes perfect sense.

  Thank-you.

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  December 23, 2013 at 11:42 am

  #1675665

  Thank you very much for the feedback.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)