WITHIN GROUP

Functions
Buy Me A Coffee

Ordered-set aggregate functions that require a sort order to compute their result: PERCENTILE_CONT (interpolated median), PERCENTILE_DISC (exact value), and MODE (most frequent value).

WITHIN GROUP is the syntax used for ordered-set aggregates (e.g., PERCENTILE_CONT, PERCENTILE_DISC, MODE) that require an ORDER BY clause to specify the ordering of input rows. Supported in PostgreSQL and Oracle as group aggregates, but SQL Server requires OVER() for window function usage.

Compatibility

Filter by Database
SQL WITHIN GROUP Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Not Supported - No PERCENTILE_CONT, PERCENTILE_DISC, or MODE. Median requires a workaround with subqueries or variables.
PostgreSQL Native 9.4 PERCENTILE_CONT, PERCENTILE_DISC, and MODE() all supported as true group aggregates (no OVER clause required). PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual data value.
SQL Server Native 2012 PERCENTILE_CONT and PERCENTILE_DISC supported, but ONLY as window functions — they require an OVER() clause and cannot be used as standalone group aggregates. No MODE() function.
Oracle Native 8i PERCENTILE_CONT and PERCENTILE_DISC supported as both group aggregates and analytic functions. No MODE() — use STATS_MODE(expr) instead (added in Oracle 10g).
SQLite Not Supported - No ordered-set aggregates.

Details

Key gotcha: SQL Server only supports these as window functions (OVER() clause required), not standalone group aggregates. PostgreSQL is the only engine with MODE(). Oracle uses STATS_MODE() as an equivalent.

Standard Syntax

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary, MODE() WITHIN GROUP (ORDER BY dept_id) AS most_common_dept FROM employees;

Version Support

MySQL: Not supported PostgreSQL: Native since 9.4 SQL Server: Native since 2012 Oracle: Native since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL Alternative syntax

No PERCENTILE_CONT, PERCENTILE_DISC, or MODE. Median requires a workaround with subqueries or variables.

-- Median workaround (MySQL 8.0+): WITH ranked AS ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS total FROM employees ) SELECT AVG(salary) FROM ranked WHERE rn IN (FLOOR((total+1)/2), CEIL((total+1)/2));

PostgreSQL Native syntax

PERCENTILE_CONT, PERCENTILE_DISC, and MODE() all supported as true group aggregates (no OVER clause required). PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual data value.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_exact, MODE() WITHIN GROUP (ORDER BY dept_id) AS most_common_dept FROM employees;

SQL Server Native syntax

PERCENTILE_CONT and PERCENTILE_DISC supported, but ONLY as window functions — they require an OVER() clause and cannot be used as standalone group aggregates. No MODE() function.

-- NOTE: requires OVER() in SQL Server: SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_exact FROM employees;

Oracle Native syntax

PERCENTILE_CONT and PERCENTILE_DISC supported as both group aggregates and analytic functions. No MODE() — use STATS_MODE(expr) instead (added in Oracle 10g).

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees; SELECT STATS_MODE(dept_id) AS most_common_dept FROM employees;

SQLite Alternative syntax

No ordered-set aggregates.

-- Not supported.