When I attempt to parse the query below, it does not parse. Could you please tell me where the issue is? declare @row_number int declare @acct_no bigint declare @channel_name nvarchar SET @row_number=0 select c.Account_Id, c.Channel_Name, c.Last_Refresh_Time from ( 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 ( select a.Account_Id, a.Channel_Name, a.Last_Refresh_Time from ( 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 ) a order by Last_Refresh_Time desc ) b ) c where c.rnk=1
You've got an ORDER BY clause in an inline / subquery without a TOP / OFFSET / FOR XML. I'm a bit suspicious of the mixture of setting local variables while generating resultsets, eg: .... SELECT ... @acct_no = b.Account_Id as Account_Id .... but that could just be me being overly cautious.