分区和密集等级怀疑 [英] partition and dense rank doubt

问题描述

方案1 - 第一个选择语句的顺序是

-

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按订单排序

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 ,

）



场景2 ---如果我评论第三个选择语句我得到按顺序排列的第一个选择语句

)

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

）





这是否意味着排名/密集排名具有更高的优先级...意味着如果两个密集排名/排名

和rownumber存在.. [两者都有订单，。，我猜订单依旧是两个] ,,,然后密集等级/等级顺序具有更高的优先级和按顺序排列按密集等级/等级并忽略row_num的顺序

)


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

推荐答案

我认为，我们不能谈论排名函数之间的优先级，因为它们是不确定的。它们返回每行的排名值，并且根据所使用的函数，某些行可能会收到与其他行相同的值。 OVER 子句与 ORDER BY 子句（用于排名函数）确定行集的分区和排序，其中排名函数值应用于结果集。



更多：

排名函数（Transact-SQL） [ ^ ]

OVER子句（T-SQL） [ ^ ]

ORDER BY子句（T-SQL） [ ^ ]

排名功能ns：ROW_NUMBER vs RANK vs DENSE_RANK vs NTILE [ ^ ]

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[^]

这篇关于分区和密集等级怀疑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！