SQL in a Nutshell, 2nd Edition

Name

PERCENTILE_DISC

Synopsis

Determines the value in a group with the smallest cumulative distribution greater than or equal to a percentile that you specify.

SQL2003 Syntax

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

PERCENTILE_DISC(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_DISC(percentile) WITHIN GROUP (ORDER BY expression)

Oracle also allows some use of windowing syntax:

PERCENTILE_DISC (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_DISC.

Example

The following example is similar to that for PERCENTILE_CONT, except that it returns, for each group, the value closest, but not exceeding, the 60^th percentile:

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