SQL in a Nutshell, 3rd Edition

Name

PERCENTILE_DISC

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

ANSI SQL Standard Syntax

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

percentile is a number between zero and one.

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 “ANSI SQL Window Functions,” later in this chapter, for a description of partitioning.

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 to but not exceeding the 60th 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 test4GROUP BY odd;
PERCENTILE_CONT(0.50)WITHINGROUP(ORDERBYNUM)
--------------------------------------------
                                           2
                                           3