Row number with SQL query with Inner join Group By and order by

 with cte  as (select Max(ee.ID) as ID,MainID,'temp'=Count(MainID),disc.Discipline,disc.DisciplineID,'AdjustedDiscipline'=Adisc.Discipline,'AdjustedDisciplineID'=ee.AdjustedDiscipline,Title,Name,'FMSID'=RIGHT('00000'+convert(varchar(6),ee.FMSID),6),Gender,Category,RosterCategory,DOB,AppointmentDate,AppointmentDateSS,'PresentDesignation'=ids.Designation,AppointmentDatePresent,PresentInstitutePostingDate,'Institute'=inst.I_Aym+', '+inst.I_City,'InstituteID'=inst.InstituteID_cms,DOR,'SMD'=smd.Div_Name,'SMDID'=smd.Div_Code,Mobile,ee.Email,Remarks,InstituteRemarks,'State'=st.State_Name,'StateID'=st.State_Id,DeputationFrom,DeputationTo,StudyLeaveFrom,StudyLeaveTo,WSP,'RegionalStation'=ip.I_Aym+','+ip.I_City,ee.DateTime,'PostHeld'=ict.CadreType,'CadreTypeID'=ee.PostHeld,'PresentDesignationID'=ids.DesignationID,'AiprTime'=af.DateTime,'AiprFile'=af.AIPRFile,'VigilanceStatus'=ivt.StatusText,'VigilanceStatusId'=ivt.Id,ee.PreviousOrganization,au.emp_code,do.State_Name as domicile_name,do.State_Id as domicile_id from icms_employee ee

left join icms_Discipline disc on disc.DisciplineID=ee.Discipline

left join icms_Discipline Adisc on Adisc.DisciplineID=ee.AdjustedDiscipline

left join Permisnet_new.dbo.Institute inst on inst.InstituteID_cms=ee.Institute

left join Permisnet_new.dbo.Division smd on smd.Div_Code=inst.Div_Code 

left join Permisnet_new.dbo.State st on st.State_Id=inst.I_State_ID

left join Permisnet_new.dbo.Institute ip on ip.InstituteID_cms=ee.RegionalStation

left join icms_CadreType ict on ict.CadreTypeID=ee.PostHeld

left join icms_Designation ids on ids.DesignationID=ee.PresentDesignation

left join (select * from AIPR_Files where Flag='1' and aipr_year=YEAR(GETDATE())) af on af.FMSID=ee.FMSID--year wise AIPR

left join (SELECT  State_Id,State_Name,profile_pic,m_tongue,erp_id

  FROM Add_info ai join Permisnet_new.dbo.State s on s.State_Id=ai.domicile)  do on do.erp_id=ee.FMSID

left join (select * from icms_VigilanceStatus where Flag='1') iv on iv.FMSID=ee.FMSID

left join (select * from pms.dbo.Auth_user) au on au.erp_id=ee.FMSID

left join icms_VigilanceStatusTypes ivt on ivt.Id=iv.VigilanceStatus

where PostHeld=1 and ee.Flag='true' and Retired='false' 

group by MainID,ee.ID,disc.Discipline,disc.DisciplineID,Adisc.Discipline,ee.AdjustedDiscipline,Title,Name,ee.FMSID,Gender,Category,RosterCategory,DOB,AppointmentDate,AppointmentDateSS,ids.Designation,AppointmentDatePresent,PresentInstitutePostingDate,inst.I_Aym,inst.I_City,inst.InstituteID_cms,DOR,smd.Div_Name,smd.Div_Code,Mobile,ee.Email,Remarks,InstituteRemarks,st.State_Name,st.State_Id,DeputationFrom,DeputationTo,StudyLeaveFrom,StudyLeaveTo,WSP,ip.I_Name,ip.i_aym,ip.I_City,ee.DateTime,ict.CadreType,ee.PostHeld,ids.DesignationID,af.DateTime,af.AIPRFile,ivt.StatusText,ivt.Id,ee.PreviousOrganization,au.emp_code,do.State_Name,do.State_Id )

select ROW_NUMBER() over(order by Institute, Discipline asc) as rn, * from cte order by Institute, Discipline, PresentInstitutePostingDate

reference :-https://www.sqlshack.com/overview-of-the-sql-row-number-function/

https://communities.actian.com/s/article/Different-Results-when-Joining-on-Row-number-if-Query-is-Parallelized