Custom row number (dense_rank).sql

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) 
    
 --SQL server 2012+
    
 ;with mycte as (
 select ItemName, Qty, Rate, [Time]
  ,Sum(case when [Time] is null then 0 else 1 end ) Over(order by ItemName desc) grp
 from dbo.myTable
 )
     
 Select ItemName, Qty, Rate, [Time]  
 , dense_rank() Over(Order by grp desc) as row_numb
 from mycte 
 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
    
 */
 
 --https://docs.microsoft.com/en-us/answers/questions/882742/row-number-in-sql-query-based-on-my-inpuut.html
 
 --Viorel=1's solution:
  select *, (select count(Time) + 1 from MyTable where ItemName < t.ItemName) as [row number]
 from MyTable t
 order by ItemName