SQL in a Nutshell, 2nd Edition

Name

PERCENT_RANK

Synopsis

Generates a relative rank for a hypothetical row by dividing that row’s rank less 1 by the number of rows in the group.

SQL2003 Syntax

In the following syntax, items in the value_list correspond by position to items in the sort_list. Therefore, both lists must have the same number of expressions.

PERCENT_RANK(value_list) WITHIN GROUP (ORDER BY sort_list)
value_list ::= expression [,expression...]
sort_list ::= sort_item [,sort_item...]
sort_item ::= expression [ASC|DESC] [NULLS FIRST|NULLS LAST]

Oracle

Oracle follows the SQL2003 syntax and implements the following syntax:

PERCENT_RANK( ) OVER ([partioning] ordering)

For an explanation of the partioning and order clauses, see the section later in this chapter titled Section 4.3.

DB2, MySQL, PostgreSQl, and SQL Server

These platforms do not implement the PERCENT_RANK aggregate function.

Example

The following example determines the percentage rank of the hypothetical new row (num=4, odd=1) within each group of rows from test4, where groups are distinguished by the values in the odd column:

SELECT * FROM test4;
       NUM        ODD
---------- ----------
         0          0
         1          1
         2          0
         3          1
         3          1
         4          0
         5          1
SELECT odd, PERCENT_RANK(4,1) WITHIN GROUP (ORDER BY num, odd)
FROM test4
GROUP BY odd;
       ODD PERCENT_RANK(4,1)WITHINGROUP(ORDERBYNUM,ODD)
---------- --------------------------------------------
         0                                            1
         1                                          .75

In group odd=0, the new row comes after (0,0), (2,0), and (4,0), and thus it is position 4. The rank computation is: (4th rank - 1)/3 rows = 100%. In group ...