SQL WITHIN

What does the SQL WITHIN GROUP clause do?

WITHIN GROUP specifies the sort order that ordered-set or hypothetical-set aggregate functions apply before computing their final result.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WITHIN: PostgreSQL 9.4+, Oracle 12c+, SQL Server (not yet), MySQL 8.0+ (partial), Snowflake, Redshift (partial), BigQuery (via APPROX functions).

SQL WITHIN Full Explanation

WITHIN GROUP is part of the SQL-standard syntax for ordered-set and hypothetical-set aggregate functions such as PERCENTILE_CONT, PERCENTILE_DISC, MODE, and RANK. The clause lets you supply an ORDER BY expression list that the database must use to order the input rows prior to applying the aggregate’s mathematical logic. Without WITHIN GROUP, the function would have no deterministic way to locate, for example, the 90th percentile value or the first element in a distribution. Behavior:- Executes after the WHERE, GROUP BY, and HAVING phases but before the final SELECT projection is returned.- Works only in combination with an aggregate function that explicitly supports ordered sets. Trying to use it with SUM or COUNT raises an error.- Accepts multiple columns and ASC or DESC modifiers, mirroring the standard ORDER BY syntax.Caveats:- You cannot reference column aliases defined in the SELECT list.- NULLS FIRST | LAST support depends on the database; PostgreSQL supports it, MySQL does not (as of 8.0).- Because ordered-set aggregates often require sorting large intermediate sets, performance can degrade on big tables lacking proper indexes.

SQL WITHIN Syntax

<ordered_set_function>(value_expression) WITHIN GROUP (
    ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}],
             ...
);

SQL WITHIN Parameters

value_expression (any data type) - the column or expression whose distribution you are analyzing.
sort_expression (any comparable data type) - determines row ordering before aggregation.
[ASC (DESC]) - keyword|||optional direction; default ASC.
[NULLS FIRST (LAST]) - keyword|||optional null-placement; vendor support varies.

Example Queries Using SQL WITHIN

-- 1. Median employee salary
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

-- 2. 95th percentile page load time per site section
SELECT section,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY load_ms) AS p95
FROM   page_perf
GROUP  BY section;

-- 3. Hypothetical rank: Where would 42 fit in the distribution?
SELECT RANK(42) WITHIN GROUP (ORDER BY score) AS hypothetical_rank
FROM   exam_results;

Expected Output Using SQL WITHIN

Each query returns a single scalar (or one per group) representing a percentile, rank, or similar ordered-set aggregate
No rows are inserted, updated, or deleted

Use Cases with SQL WITHIN

Calculate medians, quartiles, or arbitrary percentiles without writing window-function hacks.
Assign hypothetical ranks to values that are not yet present in the data set.
Determine the most frequent (modal) value in a distribution.
Produce statistical summaries directly in SQL for dashboards and reports.

Common Mistakes with SQL WITHIN

Using WITHIN GROUP with a regular aggregate (e.g., SUM) and getting a syntax error.
Forgetting to supply ORDER BY inside the parentheses, which is mandatory.
Expecting column aliases from SELECT to be visible inside ORDER BY.
Assuming all databases support NULLS FIRST | LAST inside WITHIN GROUP.

First Introduced In

SQL:2003 (ordered-set aggregates)

Frequently Asked Questions

What happens if I omit ORDER BY inside WITHIN GROUP?

The database raises a syntax error because the ORDER BY list is mandatory; the aggregate needs a defined ordering to operate.

Is WITHIN GROUP faster than using a window function for percentiles?

Often yes, because the optimizer can leverage indexes and stream aggregates, but performance depends on data size and indexing.

How do I calculate multiple percentiles at once?

Call PERCENTILE_CONT multiple times with different percentile arguments inside the same SELECT clause, each with its own WITHIN GROUP.

Why does my query fail with "function does not exist"?

Your database version might not support the ordered-set function, or you are passing an unsupported data type.