The percent ranks

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

  • Nice question, thanks Steve

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

  • Good question, timely reminder, thanks Steve.

    ...

  • nice question. Thanks Steve

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

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

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

  • 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?

  • 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

  • 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....)

  • 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".

  • 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.

  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

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

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