How can i Add row No ?

Question

hi every one i have this SQL query i want to add S.no through query beacuase i want to show in gridview. select sub.SubDeptName,0 TargetCount, 0 ParticipateCount, 0 ParticipationNomination, count (EP.EID)UnPlannedParticipants from Employee_Profile EP,SubDepartment sub where EP.SubDept= sub.SubDeptID and Ep.SubDept in ( select distinct SD.SubDeptID from TR_Allocate_LineManager_D D, TR_Allocate_LineManager_M M, SubDepartment SD where D.MID= M.MID and D.SubDept_ID= SD.SubDeptID) and EP.EID not in ( select THRM.EID from TR_Set_Nomination_by_HOD THRM where THRM.EID= EP.EID and cast (THRM.Nomination_date as datetime) between '1/1/2019' and '3/31/2019' ) and EP.EID in ( select EID from TR_TRAINING_SESSIONS_D d ,TR_TRAINING_SESSIONS_M m, TR_TRAININGS TR where D.MID= m.MID and m.TRAINING_ID= TR.TID and TR.TRAINING_CATEGORY in (10,11,12) and cast (m.TRAINING_DATE as datetime) between '1/1/2019' and '3/31/2019' ) group by sub.SubDeptName thanks in advance.

Mohsin Arif · Answer

hi try this Query select ROW_NUMBER() OVER( ORDER BY sub.SubDeptName ASC ) AS Row# , sub.SubDeptName,0 TargetCount, 0 ParticipateCount, 0 ParticipationNomination, count (EP.EID)UnPlannedParticipants from Employee_Profile EP,SubDepartment sub where EP.SubDept= sub.SubDeptID and Ep.SubDept in ( select distinct SD.SubDeptID from TR_Allocate_LineManager_D D, TR_Allocate_LineManager_M M, SubDepartment SD where D.MID= M.MID and D.SubDept_ID= SD.SubDeptID) and EP.EID not in ( select THRM.EID from TR_Set_Nomination_by_HOD THRM where THRM.EID= EP.EID and cast (THRM.Nomination_date as datetime) between '1/1/2019' and '3/31/2019' ) and EP.EID in ( select EID from TR_TRAINING_SESSIONS_D d ,TR_TRAINING_SESSIONS_M m, TR_TRAININGS TR where D.MID= m.MID and m.TRAINING_ID= TR.TID and TR.TRAINING_CATEGORY in (10,11,12) and cast (m.TRAINING_DATE as datetime) between '1/1/2019' and '3/31/2019' ) group by sub.SubDeptName