SQL in a Nutshell, 2nd Edition

Name

PERCENTILE_CONT

Synopsis

Generates an interpolated value corresponding to a percentile that you specify.

SQL2003 Syntax

In the following syntax, percentile is a number between zero and one:

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

Oracle

Oracle allows only one expression in the ORDER BY clause:

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expression)

Oracle also allows some use of windowing syntax:

PERCENTILE_CONT (percentile)  WITHIN GROUP
(ORDER BY sort_list) OVER (partitioning)

See Section 4.3 later in this chapter for a description of partitioning.

DB2, MySQL, PostgreSQL, and SQL Server

These platforms do not implement PERCENTILE_CONT.

Example

The following example groups the data in test4 by the column named odd, and invokes PERCENTILE_CONT to return a 50^th percentile value for each group:

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