SQL to increase when an item changes in an ordered list

Question

I have a SQL Server 2008 table with the following data:

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

I am returning a new column, which is the number that increases as the element changes, 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 supported. Hope you can help Tim

Edit: I do not need to update the table, just return the result set through the view or query. Thanks for all your efforts.

Answer 1

 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

The version where seq is the date-time. I added an extra CTE that lists the lines sorted 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 

Answer 2

I think you want this:

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

For your second request, this 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".

Answer 3

for the first

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