Percent_Rank Problem [message #335298] |
Mon, 21 July 2008 14:47 |
SColvin
Messages: 2 Registered: 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.
|
|
|
|
|