Home » SQL & PL/SQL » SQL & PL/SQL » Percent_Rank Problem (Oracle SQL 10.2.0.4.0)
Percent_Rank Problem Mon, 21 July 2008 14:47
 SColvin Messages: 2Registered: July 2008 Location: Madison Junior Member
I'm having a problem with the Percent_Rank function, wondering if anyone has some insight. SQL is:
```SELECT
A.val,
A.rank_over,
A.percent_rank_over,
B.rank_4,
B.percent_rank_4
FROM (
SELECT
val,
RANK() OVER (ORDER BY val ASC) rank_over,
PERCENT_RANK() OVER (ORDER BY val ASC) percent_rank_over
FROM (
SELECT            1 AS val FROM DUAL
UNION ALL SELECT  2 FROM DUAL
UNION ALL SELECT  3 FROM DUAL
UNION ALL SELECT  4 FROM DUAL
UNION ALL SELECT  5 FROM DUAL
UNION ALL SELECT  6 FROM DUAL
UNION ALL SELECT  7 FROM DUAL)
GROUP BY val) A,
(
SELECT
RANK(4) WITHIN GROUP (ORDER BY val ASC) AS rank_4,
PERCENT_RANK(4) WITHIN GROUP (ORDER BY val ASC) AS percent_rank_4
FROM (
SELECT            1 AS val FROM DUAL
UNION ALL SELECT  2 FROM DUAL
UNION ALL SELECT  3 FROM DUAL
UNION ALL SELECT  4 FROM DUAL
UNION ALL SELECT  5 FROM DUAL
UNION ALL SELECT  6 FROM DUAL
UNION ALL SELECT  7 FROM DUAL)
) B
/```

Results are:
```    VAL  RANK_OVER PERCENT_RANK_OVER    RANK_4 PERCENT_RANK_4
------- ---------- ----------------- -------- --------------
1          1                 0        4     .428571429
2          2        .166666667        4     .428571429
3          3        .333333333        4     .428571429
4          4                .5        4     .428571429
5          5        .666666667        4     .428571429
6          6        .833333333        4     .428571429
7          7                 1        4     .428571429```

Can anyone tell me why PERCENT_RANK_OVER and PERCENT_RANK_4 are not the same for line 4 in the results? Am I incorrect in thinking they should be equal, did I make a mistake with my WITHIN GROUP clause? Any help appreciated.

Footnote: This is just a sample meant to show the issue, really I just want to query a dataset to rank a specific value, wondering why results are not as expected.
Re: Percent_Rank Problem [message #335311 is a reply to message #335298] Mon, 21 July 2008 16:38
 S.Rajaram Messages: 1027Registered: October 2006 Location: United Kingdom Senior Member
I think if you re-read the link you will understand why the figures are different. To give you an idea formula is something like this.

```Aggregate Function

percent_rank = (rank - 1) / (no.of_rows_in_a_group)

Analytic Function

percent_rank = (rank - 1) / (no_of_rows_in_a_partition - 1)
```

Regards

Raj
Re: Percent_Rank Problem [message #335312 is a reply to message #335311] Mon, 21 July 2008 16:42
 SColvin Messages: 2Registered: July 2008 Location: Madison Junior Member
Right, that confirms my observations, thanks for your help!
 Previous Topic: need help in my code Next Topic: Partitioning a table
Goto Forum:

Current Time: Sat Jun 24 07:23:39 CDT 2017

Total time taken to generate the page: 0.14910 seconds