2

I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way:

select Term, Frequency, percent_rank() over (order by Frequency desc) * 100
from Words

But when I look at the results, instead of seeing a column that starts from 0 and goes up to 100, I see a column that starts from 0 and goes up to 37.xxxx.

Though BOL does not explicitly mention that the result is distributed over 0-100 scale, my understanding from the word percent made me use this ranking function.

What do I miss here?

9

This will happen if you have ties for the lowest frequency

The formula for figuring out the percentile rank is the following (rk -1)/(rn -1) where rk equals the rank of the value and rn equals the count of the items.

The below shows an example along with a calculated column showing how the PERCENT_RANK is calculated.

As the highest RANK is 3 when ordered descending and there are 7 rows. The (rk -1)/(rn -1) is (3-1)/(7 -1) = 2/6 = 33.3%

SELECT *, 
        RANK() OVER (order by Frequency  desc) AS  Rank1,
        1e2 * (RANK() OVER (order by Frequency  desc) - 1)/(count(*) over() - 1) AS Calc1,
        100 * PERCENT_RANK() OVER (order by Frequency  desc) AS Percent_Rank1, 

        RANK() OVER (order by Frequency  asc) AS  Rank2,
        1e2 * (RANK() OVER (order by Frequency  ASC) - 1)/(count(*) over() - 1) AS  Calc2,
        100 * PERCENT_RANK() OVER (order by Frequency ) AS  Percent_Rank2
from (VALUES (1),(1),(1),(1),(1),(2),(3))V(Frequency )

+-----------+-------+------------------+------------------+-------+------------------+------------------+
| Frequency | Rank1 |      Calc1       |  Percent_Rank1   | Rank2 |      Calc2       |  Percent_Rank2   |
+-----------+-------+------------------+------------------+-------+------------------+------------------+
|         1 |     3 | 33.3333333333333 | 33.3333333333333 |     1 |                0 |                0 |
|         1 |     3 | 33.3333333333333 | 33.3333333333333 |     1 |                0 |                0 |
|         1 |     3 | 33.3333333333333 | 33.3333333333333 |     1 |                0 |                0 |
|         1 |     3 | 33.3333333333333 | 33.3333333333333 |     1 |                0 |                0 |
|         1 |     3 | 33.3333333333333 | 33.3333333333333 |     1 |                0 |                0 |
|         2 |     2 | 16.6666666666667 | 16.6666666666667 |     6 | 83.3333333333333 | 83.3333333333333 |
|         3 |     1 |                0 |                0 |     7 |              100 |              100 |
+-----------+-------+------------------+------------------+-------+------------------+------------------+
  • What does tie mean? – Saeed Neamati Jul 15 '16 at 15:29
  • @SaeedNeamati - Rows with the same value for Frequency. 1 in my example. – Martin Smith Jul 15 '16 at 15:29
  • Thank you so much for your beautiful demonstration. So, what should I do to achieve my requirement? How do I force it to expand into 1-100 scale? – Saeed Neamati Jul 15 '16 at 15:34
  • 4
    @SaeedNeamati - Well at this point you're making up your own non standard functionality so you need to start by specifying how it should work. You should ask a new question with example data, desired results and ask how to achieve it. – Martin Smith Jul 15 '16 at 15:40
1

I don't think PERCENT_RANK() is what you want here. I think you are looking for RANK() or DENSE_RANK(). This will rank the rows your query produces starting at 1 up to the total amount of rows returned.

The example below illustrates the different way RANK() and DENSE_RANK() handle the ranking of rows with the same value.

DECLARE @Words TABLE
(
    Term        NVARCHAR(100),
    Frequency   BIGINT
)

INSERT  @Words
VALUES  ('a',1),
        ('b',7),
        ('C',8),
        ('d',3),
        ('e',8),
        ('f',89)


SELECT  Term, (CONVERT(MONEY, Frequency) / SUM(Frequency) OVER ()) * 100
FROM    @Words
  • I have 17 thousand words. I truly need a ranking function that returns percentage. rank function returns 0 to 17,000. – Saeed Neamati Jul 15 '16 at 15:26
  • Does this edit give you what you need? – James Anderson Jul 15 '16 at 15:37
0

To get a scale from 1 - 100 you can use the CUME_DIST() function. The formula for CUME_DIST() is np/nr, where np is the number of rows preceding or peer with the current row and nr is the number of rows.

So you revised code would look like:

select Term, Frequency, CUME_DIST() over (order by Frequency desc) * 100
from Words

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.