CODEWITHSUNDEEP
sqlt-sql
Tim
Tim

Reputation: 561

SQL to increment on change of item in a sequenced list

I have a SQL server 2008 table with the folling data in it:

seq | item
 1  |  A
 2  |  B
 3  |  C
 4  |  C
 5  |  C
 6  |  B
 7  |  D
 8  |  D
 9  |  C

I what return a new column which is a number which increments on change of item, as follows:

seq | item | Seq2
 1  |  A   |   1
 2  |  B   |   2
 3  |  C   |   3
 4  |  C   |   3
 5  |  C   |   3
 6  |  B   |   4
 7  |  D   |   5
 8  |  D   |   5
 9  |  C   |   6

The initial sequence must be maintained. Hope you can help, Tim

Edit: I don't what to update the table, just return the result set via a view or query. Thanks for all your efforts.

Upvotes: 4

Views: 1343

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

declare @T table(seq int, item char(1))

insert into @T values
( 1,    'A'),
( 2,    'B'),
( 3,    'C'),
( 4,    'C'),
( 5,    'C'),
( 6,    'B'),
( 7,    'D'),
( 8,    'D'),
( 9,    'C')

;with C as
(
  select seq,
         item,
         1 as seq2
  from @T
  where seq = 1
  union all
  select T.seq,
         T.item,
         C.seq2 + case when C.item <> T.item then 1 else 0 end
  from @T as T
    inner join C
      on T.seq - 1 = C.seq
)
select seq,
       item,
       seq2
from c       
order by seq

Update

A version where seq is a datetime. I have added an extra CTE that enumerates the rows ordered by seq.

declare @T table(seq datetime, item char(1))

insert into @T values
( getdate()+1,    'A'),
( getdate()+2,    'B'),
( getdate()+3,    'C'),
( getdate()+4,    'C'),
( getdate()+5,    'C'),
( getdate()+6,    'B'),
( getdate()+7,    'D'),
( getdate()+8,    'D'),
( getdate()+9,    'C')

;with C1 as
(
  select seq,
         item,
         row_number() over(order by seq) as rn
  from @T       
), 
C2 as
(
  select seq,
         item,
         rn,
         1 as seq2
  from C1
  where rn = 1
  union all
  select C1.seq,
         C1.item,
         C1.rn,
         C2.seq2 + case when C2.item <> C1.item then 1 else 0 end
  from C1
    inner join C2
      on C1.rn - 1 = C2.rn
)
select seq,
       item,
       seq2
from C2       
order by seq

Upvotes: 3

Gopal Sanodiya
Gopal Sanodiya

Reputation: 204

for first one

select A.seq,A.item, B.count from table_name A,(select item,count(item) count from table_name group by item) derived_tab B where A.item =B.item

Upvotes: 0

Lamak
Lamak

Reputation: 70658

I think you want this:

SELECT seq, Item, COUNT(*) OVER(PARTITION BY Item) [Count]
FROM YourTable

For your second query it would be:

SELECT seq, Item, DENSE_RANK() OVER(PARTITION BY Item ORDER BY Seq) Seq2
FROM yourTable

But in your example there is some inconsistency with the value of Item "B".

Upvotes: 4

Related Questions