scenario 1--The order by of first select statement that is
--
select row_number() over (partition by RD.Gender order by RD.Gender) as [number of males/females],
dosnt come by order by
select row_number() over (partition by RD.Gender order by RD.Gender) as [number of males/females],
RD.Gender,
dense_rank() over ( order by rd.route_id) as [TOTAL ROUTE ID],
rd.route_id as [ROUTE ID],
rd.userid,
user_name,
RD.Gender,
Address_Detail,
tg.landmark + '-- ' +tg.Colony,
case when status=1 then 'NO SHOW' else 'BOARDED' end as NoSHOw,rh.Appointment_DateTime,rh.Is_Guard,rh.Current_Occupancy
from tbl_route_detail rd
join tbl_Route_Header rh on rd.Route_ID=rh.Route_ID
join tbl_user_master tum on tum.userid=rd.UserID
join tbl_Geocode_Address tg on tum.address_id=tg.Address_ID
where rd.route_id in(8310913,
8296267 ,
8297244 ,
8314082 ,
8318029 ,
8319246 ,
)
scenario 2---if i comment the the third select statement i get the first select statement in order by
select row_number() over (partition by RD.Gender order by RD.Gender) as [number of males/females],
RD.Gender,
rd.route_id as [ROUTE ID],
rd.userid,
user_name,
RD.Gender,
Address_Detail,
tg.landmark + '-- ' +tg.Colony,
case when status=1 then 'NO SHOW' else 'BOARDED' end as NoSHOw,rh.Appointment_DateTime,rh.Is_Guard,rh.Current_Occupancy
from tbl_route_detail rd
join tbl_Route_Header rh on rd.Route_ID=rh.Route_ID
join tbl_user_master tum on tum.userid=rd.UserID
join tbl_Geocode_Address tg on tum.address_id=tg.Address_ID
where rd.route_id in(8310913,
8296267 ,
8297244 ,
8314082 ,
8318029 ,
8319246 ,
8319305 ,
8321227 ,
)
So does it mean that rank/dense rank has higher precedence ...means like if both dense rank/rank
and rownumber exists..[both having order by,,.,i guess order by is must for both],,,then dense rank /rank order by is having higher priority and order by comes as per dense rank /rank and order by of row_num is ignored