Let Us Learn Oracle - Part 43 of N [ Percentile_Cont function of Oracle ]

It acts both as Aggregate and analytic function.

Purpose: An inverse distribution function which takes a percentile value and a sort specification and returns an interpolated value that would fall into that percentile value with respect to the sort specification.It accepts any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype as it's argument and returns the same datatype as the numeric datatype of the argument.

Syntax: Percentile_Cont ( numeric_literal ) within group(Order by sort_expression [asc|desc]) OVER ([partition_by_clause])

SQL> SELECT MatchID
  2        ,MatchGroup
  3        ,MatchBetween
  4        ,ScheduleDate  
  5        ,PERCENTILE_Cont(.6) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY MatchGroup) Percentile_Cont  
  6    FROM MatchTable;

   MATCHID MATCHGROUP MATCHBETWEEN                                       SCHEDULED PERCENTILE_CONT
---------- ---------- -------------------------------------------------- --------- ---------------
         1 Group-A    India VS Australia                                 14-AUG-11               4
         2 Group-A    India VS Pakistan                                  15-AUG-11               4
         3 Group-A    India VS Newzealand                                16-AUG-11               4
         4 Group-A    Australia VS Pakistan                              17-AUG-11               4
         5 Group-A    Australia VS Newzealand                            18-AUG-11               4
         6 Group-A    Newzealand VS Pakistan                             19-AUG-11               4
         7 Group-B    USA VS WestIndies                                  20-AUG-11              10
         8 Group-B    USA VS Ireland                                     21-AUG-11              10
         9 Group-B    USA VS Bangaladesh                                 22-AUG-11              10
        10 Group-B    WestIndies VS Ireland                              23-AUG-11              10
        11 Group-B    WestIndies VS Bangaladesh                          24-AUG-11              10
        12 Group-B    Ireland VS Bangaladesh                             25-AUG-11              10

12 rows selected.

Prior to Denali CTP3, there was no inbuilt Percentile_Cont function.But we have this now.You can read more about Percentile_Cont in Sql Server from here

Hope you have enjoyed it.Thanks for reading