Click here to Skip to main content
15,441,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
scenario 1--The order by of first select statement that is
--
SQL
select row_number() over (partition by RD.Gender order by RD.Gender) as [number of males/females],
dosnt come by order by



SQL
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

SQL
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 ,
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
Posted

1 solution

I think, that we can't talk about priority between ranking functions, because they are nondeterministic. They return a ranking value for each row and depending on the function that is used, some rows might receive the same value as other rows. OVER clause with ORDER BY clause (used in ranking function) determine partitioning and ordering of a rowset in which ranking function values are applied to the result set.

More:
Ranking Functions (Transact-SQL)[^]
OVER Clause (T-SQL)[^]
ORDER BY Clause (T-SQL)[^]
Ranking Functions: ROW_NUMBER vs RANK vs DENSE_RANK vs NTILE[^]
 
Share this answer
 
Comments
anurag19289 2-Aug-13 6:04am    
Then why the order by of ranking function comes in order by ,,,,but the order by of row number doesnt come in order by ...when both the things are there in the query
Maciej Los 2-Aug-13 7:16am     CRLF
Because ORDER BY clause is used to sort data "inside" each ranking function ;) To sort result set on specified field, use ORDER BY clause in SELECT statement. SELECT ... FROM (<your query>) AS T ORDER BY [number of males/females]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900