# PERCENT_RANK does not distribute over 100

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?

This will happen if you have ties for the lowest frequency

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

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

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