partition and dense rank doubt

Question

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-Aug-13 20:46pm

anurag19289

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-08-01T22:48:00

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