PERCENTILE_DISC (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions in SQL Server. For a given percentile value P, PERCENTILE_DISC sorts the expression values in the ORDER BY clause. It then returns the value with the smallest CUME_DIST value given (with respect to the same sort specification) that is greater than or equal to P. For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values. The result is equal to a specific column value.
Transact-SQL syntax conventions
Syntax
PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )
Arguments
literal
The percentile to compute. The value must range between 0.0 and 1.0.
WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC)**
Specifies a list of values to sort and compute the percentile over. Only one order_by_expression is allowed. The default sort order is ascending. The list of values can be of any of the data types that are valid for the sort operation.
OVER ( <partition_by_clause>)**
Divides the FROM clause's result set into partitions. The percentile function is applied to these partitions. For more information, see OVER Clause (Transact-SQL). The <ORDER BY clause> and <rows or range clause>can't be specified in a PERCENTILE_DISC function.
Return Types
The return type is determined by the order_by_expression type.
Compatibility Support
Under compatibility level 110 and higher, WITHIN GROUP is a reserved keyword. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
General Remarks
Any nulls in the data set are ignored.
PERCENTILE_DISC is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.
Examples
Basic syntax example
The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find each department's median employee salary. They may not return the same value:
- PERCENTILE_CONT returns the appropriate value, even if it doesn't exist in the data set.
- PERCENTILE_DISC returns an actual set value.
USE AdventureWorks2022;
SELECT DISTINCT Name AS DepartmentName
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)
OVER (PARTITION BY Name) AS MedianCont
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate)
OVER (PARTITION BY Name) AS MedianDisc
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh
ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL;
Here is a partial result set.
DepartmentName MedianCont MedianDisc
Document Control 16.8269 16.8269
Engineering 34.375 32.6923
Executive 54.32695 48.5577
Human Resources 17.427850 16.5865
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
Basic syntax example
The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find each department's median employee salary. They may not return the same value:
- PERCENTILE_CONT returns the appropriate value, even if it doesn't exist in the data set.
- PERCENTILE_DISC returns an actual set value.
-- Uses AdventureWorks
SELECT DISTINCT DepartmentName
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY BaseRate)
OVER (PARTITION BY DepartmentName) AS MedianCont
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY BaseRate)
OVER (PARTITION BY DepartmentName) AS MedianDisc
FROM dbo.DimEmployee;
Here is a partial result set.
DepartmentName MedianCont MedianDisc
-------------------- ---------- ----------
Document Control 16.826900 16.8269
Engineering 34.375000 32.6923
Human Resources 17.427850 16.5865
Shipping and Receiving 9.250000 9.0000