🪟

MariaDB Window Functions

MariaDB

Complete reference for MariaDB window functions covering ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, PERCENTILE_CONT, and PERCENTILE_DISC. Window functions were added in MariaDB 10.2. Updated for MariaDB 11.x.

19 functions

Jump to function

ROW_NUMBER RANK DENSE_RANK PERCENT_RANK CUME_DIST NTILE LAG LEAD FIRST_VALUE LAST_VALUE NTH_VALUE SUM AVG COUNT MAX MIN PERCENTILE_CONT PERCENTILE_DISC MEDIAN

What are MariaDB Window Functions?

MariaDB window functions (introduced in MariaDB 10.2) compute results across a set of rows related to the current row using the OVER() clause with PARTITION BY and ORDER BY. MariaDB window function syntax follows the SQL standard and is compatible with MySQL 8.0+ and PostgreSQL window function queries. Key window functions include ROW_NUMBER() for unique row numbering, RANK() and DENSE_RANK() for ranking with and without gaps, and LAG()/LEAD() for accessing previous and next row values.

ROW_NUMBER

MariaDB 10.2+→ BIGINT

Assigns a unique sequential integer to each row within the window partition, starting at 1.

Signature

ROW_NUMBER() OVER ([PARTITION BY col] ORDER BY col)

Examples

Basic row numbering by salary descending

sql

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

→-- 1, 2, 3, ... unique for every row

Partitioned row numbering per department

sql

SELECT dept, name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees;

→-- numbering restarts at 1 for each department

With explicit frame clause (frame is noted but ROW_NUMBER ignores it)

sql

SELECT name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rn FROM employees;

→-- sequential number within running frame

Simple ascending order numbering

sql

SELECT ROW_NUMBER() OVER (ORDER BY salary) AS rn, salary FROM employees;

→-- 1, 2, 3 ...

In CTE to fetch top row per partition

sql

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees) SELECT * FROM ranked WHERE rn = 1;

→-- top earner per department

⚠Anti-Pattern— Using ROW_NUMBER without ORDER BY

Omitting ORDER BY inside OVER() produces an arbitrary, non-deterministic numbering.

✓ Instead: Always specify ORDER BY inside the OVER() clause to get a predictable sequence.

The classic pattern for 'latest record per group' is to wrap ROW_NUMBER() OVER (PARTITION BY key ORDER BY date DESC) in a CTE and then filter WHERE rn = 1. This is more efficient than a correlated subquery on large tables.

example

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) SELECT * FROM ranked WHERE rn = 1;

→one row per customer — their most recent order

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added ROW_NUMBER() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

RANK

MariaDB 10.2+→ BIGINT

Assigns a rank to each row within the partition, with tied rows receiving the same rank and leaving gaps in the sequence.

Signature

RANK() OVER ([PARTITION BY col] ORDER BY col)

Examples

Basic ranking with ties sharing the same rank

sql

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;

→-- 1, 1, 3, 4 ... (gaps after ties)

Partitioned ranking within each department

sql

SELECT dept, name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees;

→-- rank resets per department

With frame clause (typically ignored for ranking functions)

sql

SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rnk FROM employees;

→-- rank within frame

Contest leaderboard with shared ranks for ties

sql

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rnk FROM contest_results;

→-- tied scores share a rank

In CTE to fetch top-N per partition

sql

WITH ranked AS (SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees) SELECT * FROM ranked WHERE rnk <= 3;

→-- top 3 salaries per department (may return more than 3 rows if ties exist)

⚠Anti-Pattern— Expecting exactly N rows with RANK() <= N

If there are ties at the boundary, RANK() can return more rows than expected.

✓ Instead: Use ROW_NUMBER() for strict top-N limiting, or accept the extra tied rows intentionally.

RANK assigns the same number to tied rows but skips the next rank(s). If two rows tie for rank 2, the next row gets rank 4 (not 3). Use DENSE_RANK when you want sequential ranks without gaps in the leaderboard.

example

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS r, DENSE_RANK() OVER (ORDER BY score DESC) AS dr FROM leaderboard;

→ties at rank 2 show r=2 for both but r=4 next; dr=2 and dr=3 next

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added RANK() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

DENSE_RANK

MariaDB 10.2+→ BIGINT

Assigns a rank to each row within the partition with no gaps in ranking values when ties occur.

Signature

DENSE_RANK() OVER ([PARTITION BY col] ORDER BY col)

Examples

Dense ranking — consecutive values even when ties exist

sql

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk FROM employees;

→-- 1, 1, 2, 3 ... (no gaps after ties)

Partitioned dense ranking within each department

sql

SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk FROM employees;

→-- dense rank resets per department

With frame clause

sql

SELECT name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS drnk FROM employees;

→-- dense rank within frame

Product revenue ranking without gaps

sql

SELECT product, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS drnk FROM sales;

→-- 1, 2, 2, 3 ...

In CTE to fetch second-tier salary per department

sql

WITH ranked AS (SELECT *, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk FROM employees) SELECT * FROM ranked WHERE drnk = 2;

→-- second-highest salary earners per department

⚠Anti-Pattern— Confusing DENSE_RANK with RANK

RANK skips numbers after ties (1,1,3), whereas DENSE_RANK does not (1,1,2); mixing them up leads to wrong nth-place logic.

✓ Instead: Use DENSE_RANK when nth-place semantics must be consecutive regardless of ties.

Because DENSE_RANK never skips numbers, it is perfect for mapping scores to letter grades, customer tiers, or prize brackets where every rank level must be occupied.

example

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS tier FROM students;

→tier 1 for top scorers, tier 2 for next group — no gaps even with ties

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added DENSE_RANK() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

PERCENT_RANK

MariaDB 10.2+→ DOUBLE

Calculates the relative rank of a row as a percentage, returning a value between 0 and 1 inclusive.

Signature

PERCENT_RANK() OVER ([PARTITION BY col] ORDER BY col)

Examples

Basic percent rank across all employees

sql

SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS pct_rnk FROM employees;

→-- 0.0, 0.25, 0.5, 0.75, 1.0 for 5 rows

Partitioned percent rank

sql

SELECT dept, name, salary, PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary) AS pct_rnk FROM employees;

→-- percent rank within each department

With frame clause

sql

SELECT name, salary, PERCENT_RANK() OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pct_rnk FROM employees;

→-- percent rank within frame

Expressing percent rank as a 0–100 percentile

sql

SELECT name, score, ROUND(PERCENT_RANK() OVER (ORDER BY score) * 100, 1) AS percentile FROM test_scores;

→-- percentile expressed as 0–100

In CTE to filter top percentile

sql

WITH pr AS (SELECT *, PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary) AS pct FROM employees) SELECT * FROM pr WHERE pct >= 0.8;

→-- top 20% earners per department

⚠Anti-Pattern— Using PERCENT_RANK as a direct percentile value

PERCENT_RANK gives relative rank position, not a true distribution percentile; it equals 0 for the lowest value regardless of the distribution.

✓ Instead: Use PERCENTILE_CONT or PERCENTILE_DISC for true percentile calculations.

PERCENT_RANK is calculated as (rank - 1) / (total_rows - 1). The first row always returns 0.0 and is useful for percentile-based filtering, such as finding the top 10% of performers.

example

SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank FROM employees;

→top earner: pct_rank = 1.0, median earner: pct_rank ≈ 0.5

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added PERCENT_RANK() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

CUME_DIST

MariaDB 10.2+→ DOUBLE

Returns the cumulative distribution of a row within its partition: the proportion of rows with values less than or equal to the current row's value.

Signature

CUME_DIST() OVER ([PARTITION BY col] ORDER BY col)

Examples

Cumulative distribution across all employees

sql

SELECT name, salary, CUME_DIST() OVER (ORDER BY salary) AS cume FROM employees;

→-- 0.2, 0.4, 0.6, 0.8, 1.0 for 5 rows

Partitioned cumulative distribution

sql

SELECT dept, name, salary, CUME_DIST() OVER (PARTITION BY dept ORDER BY salary) AS cume FROM employees;

→-- cumulative distribution within each department

With explicit frame clause

sql

SELECT name, salary, CUME_DIST() OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume FROM employees;

→-- cumulative dist within frame

Express as a percentage

sql

SELECT name, salary, ROUND(CUME_DIST() OVER (ORDER BY salary) * 100, 1) AS cume_pct FROM employees;

→-- cumulative percentage

In CTE to select bottom half by salary

sql

WITH cd AS (SELECT *, CUME_DIST() OVER (PARTITION BY dept ORDER BY salary) AS cume FROM employees) SELECT * FROM cd WHERE cume <= 0.5;

→-- bottom 50% earners per department

⚠Anti-Pattern— Confusing CUME_DIST with PERCENT_RANK

CUME_DIST counts rows <= current row; PERCENT_RANK counts rows < current row — they differ especially at the boundaries.

✓ Instead: Use CUME_DIST when you need a value from (0,1] and PERCENT_RANK when you need [0,1).

CUME_DIST returns the cumulative distribution: the proportion of rows with values less than or equal to the current row's value. It is always between 0 and 1 (unlike PERCENT_RANK where the top row is exactly 1 and the bottom is 0).

example

SELECT product, price, CUME_DIST() OVER (ORDER BY price) AS cdf FROM products;

→products cheaper than 80% of the catalog have cume_dist ≤ 0.8

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added CUME_DIST() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

NTILE

MariaDB 10.2+→ BIGINT

Distributes rows into a specified number of ranked buckets and returns the bucket number (1 through N) for each row.

Signature

NTILE(N) OVER ([PARTITION BY col] ORDER BY col)

Parameters

Parameter	Type	Description
N	INTEGER	Number of buckets to divide rows into; must be a positive integer

Examples

Split employees into salary quartiles

sql

SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

→-- 1, 1, 2, 3, 4 ... (quartile bucket 1–4)

Partitioned quartile ranking within each department

sql

SELECT dept, name, salary, NTILE(4) OVER (PARTITION BY dept ORDER BY salary) AS quartile FROM employees;

→-- quartile resets per department

With frame clause

sql

SELECT name, salary, NTILE(10) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS decile FROM employees;

→-- decile within frame

Divide students into three performance tiers

sql

SELECT name, score, NTILE(3) OVER (ORDER BY score DESC) AS tier FROM exam_results;

→-- 1=top third, 2=middle, 3=bottom

In CTE to get top quintile products

sql

WITH buckets AS (SELECT *, NTILE(5) OVER (ORDER BY revenue DESC) AS quintile FROM products) SELECT * FROM buckets WHERE quintile = 1;

→-- top 20% revenue products

⚠Anti-Pattern— Using NTILE(N) expecting equal-sized buckets

NTILE distributes remainder rows to the first buckets, so bucket sizes can differ by one when rows don't divide evenly.

✓ Instead: Document that NTILE buckets can be unequal, or use integer arithmetic to compute bucket sizes manually.

NTILE(4) assigns each row to a quartile (1-4). When the row count is not exactly divisible by N, the first buckets receive one extra row. Use NTILE for A/B group assignment, histograms, and percentile-based segmentation.

example

SELECT customer_id, total_spend, NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile FROM customer_summary;

→quartile 1 = top 25% spenders, quartile 4 = bottom 25%

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added NTILE() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

LAG

MariaDB 10.2+→ ANY

Returns the value of an expression from a previous row within the partition at a given offset, without requiring a self-join.

Signatures

LAG(expr) OVER ([PARTITION BY col] ORDER BY col)

LAG(expr, offset) OVER ([PARTITION BY col] ORDER BY col)

LAG(expr, offset, default) OVER ([PARTITION BY col] ORDER BY col)

Parameters

Parameter	Type	Description
expr	ANY	Expression whose value from a prior row is returned
offset	INTEGER	Number of rows back to look; defaults to 1
default	ANY	Value to return when the offset goes before the first row; defaults to NULL

Examples

Access previous row's salary in hire-date order

sql

SELECT name, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees;

→-- NULL for first row, then previous salary

Partitioned LAG within each department

sql

SELECT dept, name, salary, LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS prev_sal FROM employees;

→-- previous salary within the same department

With frame clause

sql

SELECT name, LAG(amount) OVER (PARTITION BY account_id ORDER BY txn_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_amount FROM transactions;

→-- previous amount within frame

Period-over-period change with explicit offset and default

sql

SELECT date, revenue, LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue, revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) AS change FROM sales;

→-- daily revenue change with 0 default for first row

In CTE to compute percentage change

sql

WITH lagged AS (SELECT *, LAG(close_price) OVER (PARTITION BY ticker ORDER BY trade_date) AS prev_close FROM stock_prices) SELECT ticker, trade_date, ROUND((close_price - prev_close) / prev_close * 100, 2) AS pct_change FROM lagged;

→-- daily percentage change per stock

⚠Anti-Pattern— Self-join to access a prior row

Writing correlated subqueries or self-joins to access the previous row is verbose and performs poorly on large tables.

✓ Instead: Use LAG() for a concise, set-based approach that the optimizer handles efficiently.

LAG(col, 1) retrieves the previous row's value in the partition order, enabling difference and percentage-change calculations without a self-join. Provide a default third argument to avoid NULL on the first row of each partition.

example

SELECT month, revenue, revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS mom_change FROM monthly_sales;

→month: 2024-03, revenue: 50000, mom_change: +5000

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added LAG() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

LEAD

MariaDB 10.2+→ ANY

Returns the value of an expression from a subsequent row within the partition at a given offset, without requiring a self-join.

Signatures

LEAD(expr) OVER ([PARTITION BY col] ORDER BY col)

LEAD(expr, offset) OVER ([PARTITION BY col] ORDER BY col)

LEAD(expr, offset, default) OVER ([PARTITION BY col] ORDER BY col)

Parameters

Parameter	Type	Description
expr	ANY	Expression whose value from a following row is returned
offset	INTEGER	Number of rows forward to look; defaults to 1
default	ANY	Value to return when the offset goes beyond the last row; defaults to NULL

Examples

Look ahead to next hire date

sql

SELECT name, hire_date, LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire FROM employees;

→-- next hire date; NULL for last row

Partitioned LEAD within each department

sql

SELECT dept, name, salary, LEAD(salary) OVER (PARTITION BY dept ORDER BY salary) AS next_sal FROM employees;

→-- next higher salary within the same department

With frame clause

sql

SELECT name, LEAD(amount) OVER (PARTITION BY account_id ORDER BY txn_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS next_amount FROM transactions;

→-- next amount within frame

With explicit offset and default

sql

SELECT date, revenue, LEAD(revenue, 1, 0) OVER (ORDER BY date) AS next_revenue FROM sales;

→-- next day revenue with 0 default for last row

In CTE to compute time between events

sql

WITH led AS (SELECT *, LEAD(event_time) OVER (PARTITION BY session_id ORDER BY event_time) AS next_event FROM page_events) SELECT session_id, event_time, TIMESTAMPDIFF(SECOND, event_time, next_event) AS time_on_page FROM led;

→-- seconds spent on each page

⚠Anti-Pattern— Using LEAD without ORDER BY

Without ORDER BY in the OVER clause, 'next row' is undefined and results are non-deterministic.

✓ Instead: Always specify ORDER BY within OVER() to define a meaningful row ordering for LEAD.

LEAD(col, 1) accesses the next row's value in the window. Use it for session analysis (time to next event), forecasting gap calculations, or detecting state transitions where the current row needs awareness of the following state.

example

SELECT session_id, event_time, LEAD(event_time) OVER (PARTITION BY session_id ORDER BY event_time) - event_time AS time_to_next FROM events;

→time between each event and the next within the same session

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added LEAD() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; behavior is the same.

FIRST_VALUE

MariaDB 10.2+→ ANY

Returns the value of an expression from the first row of the window frame.

Signature

FIRST_VALUE(expr) OVER ([PARTITION BY col] ORDER BY col [frame_clause])

Parameters

Parameter	Type	Description
expr	ANY	Expression to evaluate from the first row of the window frame

Examples

First salary in hire-date order for all rows

sql

SELECT name, salary, FIRST_VALUE(salary) OVER (ORDER BY hire_date) AS first_salary FROM employees;

→-- salary of the first employee hired

Partitioned: highest salary per department

sql

SELECT dept, name, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_salary FROM employees;

→-- highest salary in the department

With explicit ROWS frame for running first value

sql

SELECT name, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_sal FROM employees;

→-- first salary seen so far within partition

Running first value in a time series

sql

SELECT date, close, FIRST_VALUE(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period_start_close FROM stock_prices;

→-- opening price of the period for each ticker

In CTE to get top earner per department

sql

WITH fv AS (SELECT *, FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner FROM employees) SELECT DISTINCT dept, top_earner FROM fv;

→-- top earner name per department

⚠Anti-Pattern— Expecting the first row of the partition regardless of frame

FIRST_VALUE returns the first row of the frame, not necessarily the partition; if the frame starts at CURRENT ROW, FIRST_VALUE equals the current row's value.

✓ Instead: Explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to always get the partition-wide first value.

By default FIRST_VALUE uses the default frame which may not include all rows you expect. Explicitly use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to ensure the first value over the entire partition is returned consistently.

example

SELECT dept, name, salary, FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS top_earner FROM employees;

→top_earner = highest-paid person in each department for every row

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added FIRST_VALUE() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

LAST_VALUE

MariaDB 10.2+→ ANY

Returns the value of an expression from the last row of the window frame.

Signature

LAST_VALUE(expr) OVER ([PARTITION BY col] ORDER BY col [frame_clause])

Parameters

Parameter	Type	Description
expr	ANY	Expression to evaluate from the last row of the window frame

Examples

Last salary in hire-date order — needs UNBOUNDED FOLLOWING frame

sql

SELECT name, salary, LAST_VALUE(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary FROM employees;

→-- salary of the most recently hired employee

Partitioned last value per department

sql

SELECT dept, name, salary, LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_earner FROM employees;

→-- name of highest earner (last in ASC order) per dept

Running last value — current row is always the last of the running frame

sql

SELECT name, salary, LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_sal FROM employees;

→-- most recent salary seen so far in partition

Last close price for the whole period

sql

SELECT date, close, LAST_VALUE(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS period_end_close FROM stock_prices;

→-- closing price of the period for each ticker

In CTE with DISTINCT to collapse partition result

sql

WITH lv AS (SELECT *, LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS best_earner FROM employees) SELECT DISTINCT dept, best_earner FROM lv;

→-- best earner per department

⚠Anti-Pattern— Using LAST_VALUE with the default frame

With the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame, LAST_VALUE simply returns the current row's value, not the last row of the partition.

✓ Instead: Explicitly write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the partition-level last value.

Without an explicit frame, LAST_VALUE uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which means it only 'sees' values up to and including the current row, not the true last value in the partition. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

example

SELECT name, salary, LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_earner FROM employees;

→lowest_earner = least-paid person in each department for every row

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added LAST_VALUE() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

NTH_VALUE

MariaDB 10.2+→ ANY

Returns the value of an expression from the Nth row of the window frame (1-indexed), or NULL if the frame contains fewer than N rows.

Signature

NTH_VALUE(expr, N) OVER ([PARTITION BY col] ORDER BY col [frame_clause])

Parameters

Parameter	Type	Description
expr	ANY	Expression to evaluate from the Nth row of the window frame
N	INTEGER	Row position within the frame (1-indexed); must be a positive integer

Examples

Second-highest salary across all employees

sql

SELECT name, salary, NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest FROM employees;

→-- second-highest salary repeated for every row

Partitioned: runner-up earner per department

sql

SELECT dept, name, salary, NTH_VALUE(name, 2) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS runner_up FROM employees;

→-- second-highest earner per department

With running frame — returns NULL until 3 rows accumulate

sql

SELECT name, NTH_VALUE(salary, 3) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS third_salary FROM employees;

→-- third salary seen so far (NULL if fewer than 3 rows accumulated)

NTH_VALUE(expr, 1) is equivalent to FIRST_VALUE

sql

SELECT ticker, date, NTH_VALUE(close, 1) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_close FROM stock_prices;

→-- equivalent to FIRST_VALUE

In CTE to extract second-place salary per department

sql

WITH nv AS (SELECT *, NTH_VALUE(salary, 2) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_sal FROM employees) SELECT DISTINCT dept, second_sal FROM nv;

→-- second-highest salary per department

⚠Anti-Pattern— Omitting UNBOUNDED FOLLOWING from the frame

Without extending the frame to UNBOUNDED FOLLOWING, NTH_VALUE can return NULL for early rows because the Nth row has not yet entered the frame.

✓ Instead: Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to ensure the full partition is visible.

NTH_VALUE(col, 2) returns the second row in the window order, NTH_VALUE(col, 3) the third, etc. Like LAST_VALUE, use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to access values beyond the current row's position.

example

SELECT dept, name, salary, NTH_VALUE(name, 2) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_earner FROM employees;

→second_earner = second-highest paid person per department

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added NTH_VALUE() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

SUM

MariaDB 10.2+→ DECIMAL / DOUBLE / BIGINT

Computes the sum of an expression over the window frame, enabling running totals and partitioned aggregation without collapsing rows.

Signature

SUM(expr) OVER ([PARTITION BY col] [ORDER BY col] [frame_clause])

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric expression to sum over the window frame

Examples

Grand total without collapsing rows

sql

SELECT name, salary, SUM(salary) OVER () AS total_payroll FROM employees;

→-- total payroll repeated for every row

Partitioned sum per department

sql

SELECT dept, name, salary, SUM(salary) OVER (PARTITION BY dept) AS dept_total FROM employees;

→-- department payroll repeated for each row in that dept

Running sum with ROWS frame

sql

SELECT name, salary, SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees;

→-- running payroll total within department

7-day rolling window sum

sql

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day FROM daily_sales;

→-- 7-day rolling revenue total

In CTE to compute YTD revenue

sql

WITH rs AS (SELECT *, SUM(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd FROM monthly_revenue) SELECT * FROM rs WHERE month = 12;

→-- year-to-date revenue per region at December

⚠Anti-Pattern— Using GROUP BY instead of SUM OVER for running totals

Grouping aggregates remove individual rows, requiring a self-join to produce a running total alongside row-level data.

✓ Instead: Use SUM() OVER with an ORDER BY and ROWS frame to compute running totals without losing row granularity.

SUM(col) OVER (PARTITION BY key ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) computes a running total in a single pass. Replace any correlated subquery that sums all prior rows with this window function for dramatically better performance.

example

SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders;

→running_total increases with each row in date order

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added window SUM() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

AVG

MariaDB 10.2+→ DOUBLE / DECIMAL

Computes the average of an expression over the window frame, enabling moving averages and partitioned means without collapsing rows.

Signature

AVG(expr) OVER ([PARTITION BY col] [ORDER BY col] [frame_clause])

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric expression to average over the window frame

Examples

Global average without collapsing rows

sql

SELECT name, salary, AVG(salary) OVER () AS company_avg FROM employees;

→-- company-wide average salary on every row

Partitioned average per department

sql

SELECT dept, name, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees;

→-- department average salary for each row

Running average with ROWS frame

sql

SELECT name, salary, AVG(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg FROM employees;

→-- running average salary within department

7-day moving average

sql

SELECT date, price, AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7 FROM stock_prices;

→-- 7-day moving average price

In CTE for centered moving average

sql

WITH ma AS (SELECT *, AVG(revenue) OVER (PARTITION BY product ORDER BY month ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS centered_avg FROM monthly_revenue) SELECT * FROM ma;

→-- 5-month centered moving average per product

⚠Anti-Pattern— Computing group average with a correlated subquery

Correlated subqueries that compute a GROUP BY average for each row are O(N^2) and scale poorly.

✓ Instead: Use AVG() OVER (PARTITION BY ...) for a single-pass computation.

RANGE BETWEEN N PRECEDING AND CURRENT ROW uses value-based bounds which can include varying numbers of rows when values repeat. Use ROWS BETWEEN N PRECEDING AND CURRENT ROW for a fixed-size window (e.g., exactly 7 rows for a 7-day moving average).

example

SELECT date, price, AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7 FROM stock_prices;

→ma7 = 7-day moving average, exactly 7 rows wide

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added window AVG() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

COUNT

MariaDB 10.2+→ BIGINT

Counts rows or non-NULL values within the window frame, enabling running counts and partitioned row counts without collapsing rows.

Signatures

COUNT(*) OVER ([PARTITION BY col] [ORDER BY col] [frame_clause])

COUNT(expr) OVER ([PARTITION BY col] [ORDER BY col] [frame_clause])

Parameters

Parameter	Type	Description
expr	ANY	Expression to count non-NULL values of; use * to count all rows

Examples

Total row count without collapsing

sql

SELECT name, dept, COUNT(*) OVER () AS total_employees FROM employees;

→-- total headcount on every row

Partitioned count per department

sql

SELECT dept, name, COUNT(*) OVER (PARTITION BY dept) AS dept_count FROM employees;

→-- headcount per department on every row in that dept

Running count of hires within department

sql

SELECT name, COUNT(*) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS hire_seq FROM employees;

→-- sequential hire count within department

Rolling 30-row window count

sql

SELECT date, event, COUNT(*) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS events_last_30 FROM event_log;

→-- rolling 30-row event count

In CTE to identify first order per customer

sql

WITH cc AS (SELECT *, COUNT(order_id) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS order_num FROM orders) SELECT * FROM cc WHERE order_num = 1;

→-- first order per customer

⚠Anti-Pattern— Using a subquery to get partition size for percentage calculation

Joining back to a grouped subquery to get partition size is verbose and harder to maintain.

✓ Instead: Use COUNT(*) OVER (PARTITION BY ...) to get the partition size inline.

SELECT *, COUNT(*) OVER () AS total FROM table LIMIT 20 returns both the page of results and the total row count in a single query — no separate COUNT(*) query needed. This is a clean pattern for paginated APIs.

example

SELECT id, name, COUNT(*) OVER () AS total_count FROM products WHERE active = 1 LIMIT 20 OFFSET 0;

→20 rows, each with total_count = 347 (total matching rows)

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added window COUNT() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

Common Gotchas

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

MAX

MariaDB 10.2+→ ANY

Returns the maximum value of an expression over the window frame, enabling running maximums and partitioned peak values without collapsing rows.

Signature

MAX(expr) OVER ([PARTITION BY col] [ORDER BY col] [frame_clause])

Parameters

Parameter	Type	Description
expr	ANY	Expression to find the maximum of over the window frame

Examples

Global maximum without collapsing rows

sql

SELECT name, salary, MAX(salary) OVER () AS highest_salary FROM employees;

→-- highest salary in the company on every row

Partitioned maximum per department

sql

SELECT dept, name, salary, MAX(salary) OVER (PARTITION BY dept) AS dept_max FROM employees;

→-- department maximum salary for every row in that dept

Running maximum with ROWS frame

sql

SELECT name, salary, MAX(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max FROM employees;

→-- highest salary seen so far within department

Rolling 7-day high

sql

SELECT date, high, MAX(high) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_high FROM stock_prices;

→-- 7-day rolling high

In CTE to filter rows matching the partition maximum

sql

WITH mx AS (SELECT *, MAX(salary) OVER (PARTITION BY dept) AS dept_max FROM employees) SELECT * FROM mx WHERE salary = dept_max;

→-- top earners in each department

⚠Anti-Pattern— Filtering with a correlated MAX subquery

Writing WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.dept = employees.dept) is evaluated per row.

✓ Instead: Use MAX() OVER (PARTITION BY dept) in a CTE and filter in the outer query for better readability and often better performance.

Unlike the aggregate MAX which collapses rows, the window MAX adds the maximum value as a new column on every row. Use this to calculate the percentage of maximum for each row: col / MAX(col) OVER (PARTITION BY grp).

example

SELECT dept, name, salary, MAX(salary) OVER (PARTITION BY dept) AS dept_max, salary / MAX(salary) OVER (PARTITION BY dept) AS pct_of_max FROM employees;

→each row shows salary relative to the department's top earner

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added window MAX() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

MIN

MariaDB 10.2+→ ANY

Returns the minimum value of an expression over the window frame, enabling running minimums and partitioned floor values without collapsing rows.

Signature

MIN(expr) OVER ([PARTITION BY col] [ORDER BY col] [frame_clause])

Parameters

Parameter	Type	Description
expr	ANY	Expression to find the minimum of over the window frame

Examples

Global minimum without collapsing rows

sql

SELECT name, salary, MIN(salary) OVER () AS lowest_salary FROM employees;

→-- lowest salary in the company on every row

Partitioned minimum per department

sql

SELECT dept, name, salary, MIN(salary) OVER (PARTITION BY dept) AS dept_min FROM employees;

→-- department minimum salary for every row in that dept

Running minimum with ROWS frame

sql

SELECT name, salary, MIN(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_min FROM employees;

→-- lowest salary seen so far within department

Rolling 7-day low

sql

SELECT date, low, MIN(low) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_low FROM stock_prices;

→-- 7-day rolling low

In CTE to detect all-time low price events

sql

WITH mn AS (SELECT *, MIN(price) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS hist_min FROM prices) SELECT * FROM mn WHERE price = hist_min;

→-- rows where price hit an all-time low

⚠Anti-Pattern— Using a correlated subquery to find the minimum for comparison

Correlated subqueries like (SELECT MIN(price) FROM prices p2 WHERE p2.product_id = prices.product_id) execute once per row.

✓ Instead: Use MIN() OVER (PARTITION BY product_id) for a single-scan solution.

The window MIN adds the partition minimum to every row without collapsing the result set. Use it to compute relative distance from the minimum: col - MIN(col) OVER (PARTITION BY grp) for normalization or range calculations.

example

SELECT product, price, MIN(price) OVER (PARTITION BY category) AS cat_min_price FROM products;

→every product row shows the cheapest price in its category

◆MariaDB Note

Available since MariaDB 10.2.

⇄ vs MySQL: MySQL added window MIN() in 8.0; MariaDB had it since 10.2.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PostgreSQL also supports GROUPS frame mode.

PERCENTILE_CONT

MariaDB 10.3.3+→ DOUBLE

Computes a continuous percentile value by interpolating between adjacent values in the sorted set, returning a value that may not exist in the data.

Signature

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY expr) OVER ([PARTITION BY col])

Parameters

Parameter	Type	Description
fraction	DOUBLE	Percentile fraction between 0 and 1 (e.g., 0.5 for median)
expr	NUMERIC	Expression defining the sort order for the percentile calculation

Examples

Company-wide median salary using continuous interpolation

sql

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_salary FROM employees LIMIT 1;

→-- interpolated median salary across all employees

Partitioned median per department

sql

SELECT DISTINCT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS dept_median FROM employees;

→-- median salary per department (interpolated)

75th percentile per department

sql

SELECT name, dept, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS p75_salary FROM employees;

→-- 75th percentile salary within each department

P90 latency per endpoint — common SLA metric

sql

SELECT endpoint, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY response_ms) OVER (PARTITION BY endpoint) AS p90_latency FROM api_logs;

→-- P90 latency per API endpoint

In CTE to join median back to employee rows

sql

WITH pct AS (SELECT DISTINCT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS median_sal FROM employees) SELECT e.*, pct.median_sal FROM employees e JOIN pct USING (dept);

→-- each employee row annotated with their dept median

⚠Anti-Pattern— Using AVG to estimate the median

The average is strongly skewed by outliers and is not the median; on a right-skewed salary distribution, AVG can be far above the true middle.

✓ Instead: Use PERCENTILE_CONT(0.5) for a true interpolated median.

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) returns the exact median by linear interpolation between the two middle values when there is an even count. This is the statistically correct median for continuous variables like response time or income.

example

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

→dept: 'Engineering', median_salary: 87500.00 (interpolated)

◆MariaDB Note

Available since MariaDB 10.3.3 as an ordered-set aggregate window function.

⇄ vs MySQL: MySQL does not support PERCENTILE_CONT; this is a MariaDB-specific advantage.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PERCENTILE_CONT is available since PostgreSQL 9.4.

PERCENTILE_DISC

MariaDB 10.3.3+→ ANY

Computes a discrete percentile value by returning the first actual value in the sorted set whose cumulative distribution meets or exceeds the given fraction.

Signature

PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY expr) OVER ([PARTITION BY col])

Parameters

Parameter	Type	Description
fraction	DOUBLE	Percentile fraction between 0 and 1 (e.g., 0.5 for median)
expr	ANY	Expression defining the sort order for the percentile calculation

Examples

Company-wide discrete median — returns a real observed salary

sql

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_salary FROM employees LIMIT 1;

→-- actual median value that exists in the data

Partitioned discrete median per department

sql

SELECT DISTINCT dept, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS dept_median FROM employees;

→-- discrete median salary per department

75th percentile discrete value

sql

SELECT name, dept, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS p75_salary FROM employees;

→-- 75th percentile actual salary value per department

Discrete median latency per API endpoint

sql

SELECT endpoint, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY response_ms) OVER (PARTITION BY endpoint) AS median_latency FROM api_logs;

→-- actual observed median response time per endpoint

In CTE to report discrete median per department

sql

WITH pct AS (SELECT DISTINCT dept, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS p50 FROM employees) SELECT * FROM pct ORDER BY dept;

→-- median salary per department as actual observed value

⚠Anti-Pattern— Using PERCENTILE_DISC when a smooth interpolation is needed

PERCENTILE_DISC returns an existing value; on small datasets the result can be far from the true percentile position.

✓ Instead: Use PERCENTILE_CONT for a smoothly interpolated percentile on continuous data.

PERCENTILE_DISC(0.5) returns the first actual value in the sorted order that reaches or exceeds the target percentile. Unlike PERCENTILE_CONT it never returns an interpolated value, making it suitable for integer counts, ordinal ranks, and categorical scores.

example

SELECT dept, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) AS median_score FROM exam_results GROUP BY dept;

→dept: 'Sales', median_score: 82 (actual observed score)

◆MariaDB Note

Available since MariaDB 10.3.3 as an ordered-set aggregate window function.

⇄ vs MySQL: MySQL does not support PERCENTILE_DISC; this is a MariaDB-specific advantage.

⇄ vs PostgreSQL: PostgreSQL syntax is identical; PERCENTILE_DISC is available since PostgreSQL 9.4.

MEDIAN

MariaDB 10.3.3+→ DOUBLE

Returns the median (50th percentile) of values in a group using continuous interpolation; a convenient shorthand for PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expr).

Signature

MEDIAN(expr) OVER ([PARTITION BY col])

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric expression whose median is calculated over the window

Examples

Company-wide median without collapsing rows

sql

SELECT name, salary, MEDIAN(salary) OVER () AS company_median FROM employees;

→-- interpolated median salary on every row

Partitioned median per department

sql

SELECT dept, name, salary, MEDIAN(salary) OVER (PARTITION BY dept) AS dept_median FROM employees;

→-- median salary per department on every row

With frame clause for running median

sql

SELECT name, salary, MEDIAN(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_median FROM employees;

→-- running median within department

Median sale amount per product

sql

SELECT product_id, sale_date, amount, MEDIAN(amount) OVER (PARTITION BY product_id) AS median_sale FROM sales;

→-- median sale amount per product on every row

In CTE to compare individual salary to department median

sql

WITH med AS (SELECT DISTINCT dept, MEDIAN(salary) OVER (PARTITION BY dept) AS med_sal FROM employees) SELECT e.name, e.salary, m.med_sal, e.salary - m.med_sal AS vs_median FROM employees e JOIN med m USING (dept);

→-- each employee's salary vs. department median

⚠Anti-Pattern— Computing median with AVG of middle two rows

Hand-rolling a median with ROW_NUMBER tricks and conditional aggregation is error-prone and verbose.

✓ Instead: Use MEDIAN() OVER (...) for a clean, correct, and readable median calculation.

MEDIAN(col) OVER (PARTITION BY grp) annotates every row with the median of its partition, allowing per-row comparison: how far is this value from the partition median? This pattern is common in outlier detection.

example

SELECT name, salary, MEDIAN(salary) OVER (PARTITION BY dept) AS dept_median, salary - MEDIAN(salary) OVER (PARTITION BY dept) AS vs_median FROM employees;

→vs_median shows how each person's salary deviates from the department median

◆MariaDB Note

MEDIAN is a MariaDB extension available since MariaDB 10.3.3.

⇄ vs MySQL: MySQL does not support MEDIAN as a window function.

⇄ vs PostgreSQL: PostgreSQL does not have a MEDIAN shorthand; use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expr) instead.

Related MariaDB Categories

Aggregate Functions →JSON Functions →