row number in sql query based on my inpuut

Question

Hello

This is my output coming

but i need to add row number like this below

how to do so using ms sql 2008

Thanking You

Answer 1

create table dbo.myTable  
(  
 ItemName varchar(30),  
Qty int, Rate int, [Time] int  
)  
   
 insert into myTable   
 values  
 ( 'A1',5,0 ,null),  
 ( 'A2',23,4 ,null),  
 ( 'A3',3,23 ,40),  
 ( 'A4',7,7 ,null),  
 ( 'A5',4,232 ,null),  
 ( 'B3',2,23 ,10),  
 ( 'B4',1,23 ,15),  
 ( 'B5',3,23 ,null),  
 ( 'C1',23,232 ,null),    
 ( 'C2',24,23 ,20),  
 ('C3',34,23 ,50),  
 ('C8',4,23 ,null),  
 ('C9',9,20 ,10)   
  
  
  
;with mycte as (  
select *,row_number()over( order by ItemName ) rn  
from dbo.myTable  
)  
,mycte2 as (  
select m.*,d.time_filled  
  from mycte m  
OUTER APPLY (SELECT TOP 1  Case   
when left(ItemName,1)='A' then 1   
when left(ItemName,1)='B' then 10   
when left(ItemName,1)='C' then 100 end   
*[Time]  as time_filled   
FROM mycte  
WHERE rn>= m.rn AND [Time] IS NOT NULL  
ORDER BY rn ) d  
)  
,mycte3 as (  
select *,ISNULL(time_filled-lag(time_filled) over(partition by time_filled Order by rn),1) delta  
   
from mycte2)  
,mycte4 as (  
select *, sum(delta) over(  order by rn)  grp   
from mycte3  
)  
Select ItemName, Qty, Rate, Time    
, dense_rank() Over(Order by grp) as row_numb  
from mycte4  
order by ItemName  
   
   
drop table myTable  
/*  
ItemName	Qty	Rate	Time	row_numb  
A1	5	0	NULL	1  
A2	23	4	NULL	1  
A3	3	23	40	1  
A4	7	7	NULL	2  
A5	4	232	NULL	2  
B3	2	23	10	2  
B4	1	23	15	3  
B5	3	23	NULL	4  
C1	23	232	NULL	4  
C2	24	23	20	4  
C3	34	23	50	5  
C8	4	23	NULL	6  
C9	9	20	10	6  
  
*/  

Answer 2

Check if these methods works in SQL Server 2008:

select *, (select count(Time) + 1 from MyTable where ItemName < t.ItemName) as [row number]  
from MyTable t  
order by ItemName  
  
-- or:  
  
select *, count(Time) over (order by ItemName) + case when Time is null then 1 else 0 end as [row number]  
from MyTable  
order by ItemName  
  
-- or:  
  
select *, count(Time) over (order by ItemName rows between unbounded preceding and 1 preceding ) + 1 as [row number]  
from MyTable  
order by ItemName  

It assumes that ItemName can be used for ordering.