Sequencing Numbers 2

[kpereira]

Hello:

I loved the following article, but I need to do something different and I'm new to SQL programming.

http://tek-tips.com/viewthread.cfm?SQID=859372&SPID=183&newpid=183&page=1

I am trying to take the items in the temp table and instead of sequencing them consecutively I would like to sequence them in this way:

101 1
101 1
101 2
101 2
102 1
102 1
102 2
102 2
102 3
102 3

I can't seem to get it to work this way. I have tried so many different ways that I'm going batty. Can anyone help???

Karen Pereira
"Great spirits often meet violent opposition with mediocre minds" - Albert Einstein

 

[ESquared]

--IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'tblTest') DROP TABLE tblTest
CREATE TABLE tblTest (
   Value int,
   Seq int)

INSERT INTO tblTest (Value)
   SELECT 101 UNION ALL
   SELECT 101 UNION ALL
   SELECT 101 UNION ALL
   SELECT 101 UNION ALL
   SELECT 102 UNION ALL
   SELECT 102 UNION ALL
   SELECT 102 UNION ALL
   SELECT 102 UNION ALL
   SELECT 102 UNION ALL
   SELECT 102


DECLARE
   @iSeq [red]numeric(10,1)[/red],
   @iLastVal int

UPDATE T
   SET
      @iSeq = CASE
         WHEN @iLastVal = T.Value THEN @iSeq + [red].5[/red]
         ELSE 1 END,
      @iLastVal = T.Value,
      T.Seq = [red]convert(int,floor([/red]@iSeq[red]))[/red]
   FROM tblTest T
      INNER JOIN (SELECT TOP 100 PERCENT Value
         FROM tblTest
         ORDER BY Value
      ) T2 ON T.Value = T2.Value

SELECT * FROM tblTest ORDER BY Value, Seq

DROP TABLE tblTest

The trick is to count by .5 instead of 1.

Of course, you could also do the update as previously given and then do a second update statement to fix the numbers:

[tt]UPDATE Table
SET Seq = floor((Seq -1) / 2)+1[/tt]

 

[kpereira]

Thanks a bunch!

Great logic! This is a fantastic forum!


Karen

"Great spirits often meet violent opposition with mediocre minds" - Albert Einstein