The percent ranks

Question

The percent ranks

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
January 24, 2018 at 8:55 pm

#326498

Comments posted to this topic are about the item The percent ranks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72499 More actions · Answer 1

Nice question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

HappyGeek SSCoach Points: 18726 More actions · Answer 2

HappyGeek

SSCoach

Points: 18726

January 25, 2018 at 12:37 am

#1977257

Good question, timely reminder, thanks Steve.

...

manik_anu SSCrazy Points: 2377 More actions · Answer 3

nice question. Thanks Steve

Manik
You cannot get to the top by sitting on your bottom.

call.copse SSCoach Points: 17960 More actions · Answer 4

Knew this one OK. It's Microsoft, the highest percentage is not going to be 100 now is it? 🙂

George Vobr SSCrazy Eights Points: 9916 More actions · Answer 5

Thanks for this question. A short note to the Explanation, the Docs says that the PERCENT_RANK function returns the data type float.

timwell SSCertifiable Points: 5315 More actions · Answer 6

In the example in the reference given why is the highest rate given a PERCENT_RANK of zero?

This is their rank clause, there is no DESC.
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank

Is the example incorrect?

jschmidt 17654 SSCommitted Points: 1610 More actions · Answer 7

timwell - Thursday, January 25, 2018 7:38 AM
In the example in the reference given why is the highest rate given a PERCENT_RANK of zero?
This is their rank clause, there is no DESC.
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
Is the example incorrect?

That is true, but Steve's question had a reverse order by for the result set than for the Percent_Rank(). That "desc" tripped me up.
PERCENT_RANK() OVER (ORDER BY cteLeaders.totalpts)
FROM cteLeaders
ORDER BY cteLeaders.totalpts desc

timwell SSCertifiable Points: 5315 More actions · Answer 8

jschmidt 17654 - Thursday, January 25, 2018 8:24 AM
timwell - Thursday, January 25, 2018 7:38 AM
In the example in the reference given why is the highest rate given a PERCENT_RANK of zero?
This is their rank clause, there is no DESC.
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
Is the example incorrect?
That is true, but Steve's question had a reverse order by for the result set than for the Percent_Rank(). That "desc" tripped me up.
PERCENT_RANK() OVER (ORDER BY cteLeaders.totalpts)
FROM cteLeaders
ORDER BY cteLeaders.totalpts desc

It seems like that should affect the result set but not the PERCENT_RANK.
The example also has DESC for the overall query and not for the PERCENT_RANK. It doesn't use a CTE so maybe that makes a difference?

I am on 2008 R2 so I can't try it. (or ever use it so it's just curiosity at this point....)

jschmidt 17654 SSCommitted Points: 1610 More actions · Answer 9

timwell - Thursday, January 25, 2018 9:16 AM
jschmidt 17654 - Thursday, January 25, 2018 8:24 AM
timwell - Thursday, January 25, 2018 7:38 AM
In the example in the reference given why is the highest rate given a PERCENT_RANK of zero?
This is their rank clause, there is no DESC.
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
Is the example incorrect?
That is true, but Steve's question had a reverse order by for the result set than for the Percent_Rank(). That "desc" tripped me up.
PERCENT_RANK() OVER (ORDER BY cteLeaders.totalpts)
FROM cteLeaders
ORDER BY cteLeaders.totalpts desc
It seems like that should affect the result set but not the PERCENT_RANK.
The example also has DESC for the overall query and not for the PERCENT_RANK. It doesn't use a CTE so maybe that makes a difference?
I am on 2008 R2 so I can't try it. (or ever use it so it's just curiosity at this point....)

The example in the reference also has "partition by" that breaks up the result set, while the question just had "order by".

timwell SSCertifiable Points: 5315 More actions · Answer 10

jschmidt 17654 - Thursday, January 25, 2018 9:44 AM
The example in the reference also has "partition by" that breaks up the result set, while the question just had "order by".

Oops. Sorry. My original statement was incorrect. I looked at the last row and missed that there are different ranges within the groups.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

You've just got to love some of the contradictions found in Microsoft documentation. First, they say (emphasis is mine)...

The range of values returned by PERCENT_RANK is greater than 0 and less than or equal to 1.

In the very next sentence, they say...

The first row in any set has a PERCENT_RANK of 0.

Asking the obvious rhetorical question, is the first row considered to NOT be a part of the range they speak of?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)