What wrong with the query

There appears to be a few issues with it, but one main one is that you cannot use a single select statement to both assign values to a variable as well as retrieve data. The line where you fill in @row_number and @acct_no, etc, you also just select b.Last_Refresh_Time. You can't do that.

Code written is not clear, I suggest you use temporary tables or With such as common table to write SQL statements
I hope your structure is clea



<pre lang="SQL">declare @row_number int
declare @acct_no bigint
declare @channel_name nvarchar
 
SET @row_number=0


WITH a AS
(select
 a.acct_id  as Account_Id,
dp.name as Channel_Name,
dc.last_call_made_for_access As Last_Refresh_Time
from
stg.delivered_cap dc 
inner join stg.ordered_product tp on
tp.ord_prod_id=dc.ord_prod_id
 inner join stg.acct_order_item ai on
 tp.ord_prod_id = ai.ord_prod_id
inner join stg.acct_order ao on
ai.order_id = ao.order_id
 inner join stg.account a on
ao.acct_id = a.acct_id
left join  stg.delivery_point dp   on
 dp.del_point_id = dc.del_point_id
left join  stg.account_status dcact   on
dcact.acct_stat_id=dc.acct_status_id
where
 a.bu_id = 49
and dc.last_call_made_for_access is not null
),
b AS
(
select
a.Account_Id,
a.Channel_Name,
a.Last_Refresh_Time 
FROM a order by Last_Refresh_Time desc
),
c AS
(
select
@row_number=CASE WHEN @acct_no = b.Account_Id and @channel_name = b.Channel_Name 
THEN @row_number + 1
ELSE 1
END AS rnk,
@acct_no=  b.Account_Id as Account_Id,
@channel_name= b.Channel_Name as Channel_Name,
 b.Last_Refresh_Time
FROM b
)
select
 c.Account_Id,
 c.Channel_Name,
 c.Last_Refresh_Time 
 FROM c where
c.rnk=1</pre>