PERCENTILE_DISC() function syntax with an example in SQL Server

PERCENTILE_DISC() function syntax with an example in SQL Server

Introduction: 

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. Watch more ASP.NET, C#.NET, Jquery, Sql Server, HTML5, HTML, CSS, JQUERY UI Interview questions from here. 

Description: 

·        This function computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset.

·        PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value.

·        PERCENTILE_DISC () function will takes a parameter, which indicates the offset and it should be between 0 and 1.

Example:

USE AdventureWorks

GO

SELECT SalesOrderID, OrderQty, ProductID,

CUME_DIST () OVER (PARTITION BY SalesOrderID

ORDER BY ProductID) AS Cdist,

PERCENTILE_DISC (0.6) WITHIN GROUP (ORDER BY ProductID)

OVER (PARTITION BY SalesOrderID) AS PercentileDisc

FROM Sales.SalesOrderDetail

WHERE SalesOrderID IN (43670, 43669, 43667, 43663)

ORDER BY SalesOrderID DESC

GO

Result:

SalesOrderID	OrderQty	ProductID	Cdist	PercentileDisc
43670	1	709	0.25	773
43670	2	710	0.5	773
43670	2	773	0.75	773
43670	1	776	1	773
43669	1	747	1	747
43667	3	710	0.25	775
43667	1	773	0.5	775
43667	1	775	0.75	775
43667	1	778	1	775
43663	1	760	1	760

If you have any queries or suggestions, please feel free to ask in comments section.