The median lesson used PERCENTILE_CONT(0.5), the 50th percentile. The same function works for any percentile, Beacon Robotics' finance team wants the 90th-percentile salary in a department, the value below which 90% of salaries fall, not just the middle one.
Learning Objectives
- Define a percentile, and the real difference between PERCENTILE_CONT (interpolated) and PERCENTILE_DISC (an actual value from the data).
- Know which of these six databases support them as a plain aggregate, which only as a window function, and which don't support them at all.
- Compute a specific percentile, correctly, on at least one database from each of those three groups.
Sort the Group
Every value, smallest to largest
Find the Percentile Position
e.g. 90% of the way through
PERCENTILE_CONT or _DISC
Interpolated, or an actual row's value
CONT vs DISC
PERCENTILE_CONT interpolates between the two nearest values when the requested percentile falls between them, a continuous result that might not actually appear anywhere in the data. PERCENTILE_DISC instead picks the first actual value from the sorted data whose position reaches the requested percentile, always a real value that genuinely exists in the group, never an interpolated one.
Support, by Database
| Database | Support |
|---|---|
| PostgreSQL / Oracle | A plain, ordered-set aggregate: PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY col), no OVER() needed. |
| SQL Server / MariaDB (10.3.3+) | A window function only: PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY col) OVER (), OVER() is mandatory on both, returning the same value repeated on every row. |
| MySQL / SQLite | No built-in support at all, neither PERCENTILE_CONT nor PERCENTILE_DISC, needs a manual, position-based emulation. |
MariaDB diverges from MySQL here, despite sharing most other syntax, MariaDB added these as window functions in 10.3.3, MySQL has never added an equivalent.
The 90th Percentile, by Group
SELECT department_id,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY department_id;SELECT DISTINCT department_id,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary)
OVER (PARTITION BY department_id) AS p90_salary
FROM employees;Try It Yourself
Find the 25th-percentile salary (the first quartile) across the whole employees table, using PERCENTILE_DISC, on PostgreSQL.
SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY salary) AS p25_salary FROM employees;
Frequently Asked Questions
Why does SQL Server's version repeat the same value across every row, instead of one row per group?
Because OVER() makes it a window function there, which by design returns one output row per input row. DISTINCT, or an outer GROUP BY/aggregation, is needed to collapse that repeated result down to one row per group, the same quirk the median lesson covered for PERCENTILE_CONT(0.5).
Can PERCENTILE_CONT ever return a value that isn't actually in the data?
Yes, that's specifically what makes it CONT (continuous) rather than DISC (discrete), it interpolates between the two nearest real values when the requested percentile falls between them. PERCENTILE_DISC never does this, it always returns one of the real, existing values.
Common Beginner Mistakes
Expecting PERCENTILE_CONT to work on MySQL
It doesn't exist there at all, despite MariaDB, MySQL's close relative, having added it in 10.3.3. MySQL itself has no PERCENTILE_CONT or PERCENTILE_DISC, a manual, position-based calculation is the only option there.
Forgetting OVER() on SQL Server or MariaDB, expecting a plain-aggregate call like PostgreSQL's
That's a syntax error on both, PERCENTILE_CONT and PERCENTILE_DISC only exist as window functions there, OVER() (even empty, OVER()) is mandatory, unlike their plain-aggregate form on PostgreSQL and Oracle.
Best Practices
- Choose PERCENTILE_DISC specifically when the result must be a real value that exists in the data, not an interpolated one, picking an actual employee's salary rather than a value nobody earns.
- On SQL Server and MariaDB, wrap the window-function call with DISTINCT or an outer aggregation step to get one row per group.
- On MySQL and SQLite, isolate the manual percentile emulation in a reusable view, it's easy to get the position/interpolation math wrong under pressure.
Interview Questions
What's the real difference between PERCENTILE_CONT and PERCENTILE_DISC?
PERCENTILE_CONT interpolates between the two nearest sorted values when the requested percentile falls between them, so its result might be a value that doesn't actually appear in the data. PERCENTILE_DISC instead returns an actual value from the sorted data, the first one whose position reaches the requested percentile, never an interpolated number.
Which of these six databases support PERCENTILE_CONT as a plain GROUP BY aggregate, and which require a window function?
PostgreSQL and Oracle support it as a plain ordered-set aggregate, WITHIN GROUP (ORDER BY ...) with no OVER() needed. SQL Server and MariaDB (10.3.3+) only support it as a window function, OVER() is mandatory on both, returning the value repeated on every row of the partition. MySQL and SQLite have no built-in support at all.
Summary
PERCENTILE_CONT interpolates, PERCENTILE_DISC picks a real value, for any percentile, not just the median's 50th. PostgreSQL and Oracle support both as a plain aggregate, SQL Server and MariaDB only as a window function requiring OVER(), MySQL and SQLite have no built-in support at all.
What's Next?
A shift from statistics to bits: BIT_AND, BIT_OR, and BIT_XOR, aggregating a group's values bit-by-bit, genuinely available on only three of these six databases.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →