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 BYsort_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 BYexpression
)
Oracle also allows some use of windowing syntax:
PERCENTILE_DISC (percentile
) WITHIN GROUP (ORDER BYsort_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 60th percentile:
SELECT * FROM test4;
NUM ODD ---------- ---------- 0 0 1 1 2 0 3 1 3 1 4 0 5 1SELECT odd, PERCENTILE_DISC(0.60) WITHIN GROUP (ORDER BY NUM)
FROM test4
GROUP BY odd;
PERCENTILE_CONT(0.50)WITHINGROUP(ORDERBYNUM) -------------------------------------------- 2 3
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.