Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 40 ยท Functions and Window Functions

Window Functions in MariaDB

Learn MariaDB window functions (10.2+): OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, running totals and frames, with runnable examples.

A normal aggregate with GROUP BY collapses rows. A window function computes a value across a set of related rows but keeps every row in the result. That makes questions like "rank employees inside their department", "show each order next to the previous one", or "running total by day" short and fast instead of needing self-joins and correlated subqueries.

MariaDB supports window functions from version 10.2. Everything in this lesson runs in the in-browser engine, so you can try each query.

The OVER clause

A window function is any function followed by OVER (...):

SQL
function(args) OVER (
  PARTITION BY col, ...     -- split into independent groups (optional)
  ORDER BY col, ...         -- order inside each partition (optional)
  frame_clause              -- which rows around the current one (optional)
)

Empty OVER () means "the whole result is one window".

SQL Aggregate next to every row ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Ranking functions

Function Ties Example for scores 90, 90, 80
ROW_NUMBER() arbitrary but unique numbers 1, 2, 3
RANK() same rank, then a gap 1, 1, 3
DENSE_RANK() same rank, no gap 1, 1, 2
NTILE(n) splits into n buckets quartiles with NTILE(4)
PERCENT_RANK(), CUME_DIST() relative position 0..1
SQL
SELECT first_name, department_id, salary,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

The WINDOW w AS (...) clause names a window so several functions can share one definition. Always add a tie-breaker (ORDER BY salary DESC, id) to ROW_NUMBER if you need repeatable results.

SQL Rank within each department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Top N per group

You cannot use a window function in WHERE, because windows are computed after filtering. Put the query in a derived table or a CTE and filter outside:

SQL Top 2 earners per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

This pattern replaces the old trick of correlated subqueries with LIMIT, which MariaDB does not allow inside IN subqueries anyway.

LAG, LEAD and the value functions

LAG(col, n, default) looks n rows back inside the partition, LEAD looks forward. FIRST_VALUE, LAST_VALUE and NTH_VALUE pick a row from the frame.

SQL
SELECT order_date, amount,
       LAG(amount)  OVER (ORDER BY order_date) AS previous,
       amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS change
FROM daily_sales;
SQL Day-over-day change

Press Run (or Ctrl + Enter) to see the result here.

Running totals and frames

Aggregates used as window functions with an ORDER BY become cumulative. The frame says exactly which rows count:

SQL
SUM(amount) OVER (ORDER BY day)                                   -- running total
SUM(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  -- 3-day moving sum
AVG(amount) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)   -- centered average

Pitfall: when you write ORDER BY without a frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the sort key are all included together, so a running total over a column with duplicate days jumps by the sum of the ties. Use ROWS when you want strictly row-by-row behaviour. LAST_VALUE is the classic victim: with the default frame it returns the current row's value, not the last one in the partition, unless you widen the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

SQL Running total and 3-day average

Press Run (or Ctrl + Enter) to see the result here.

Buckets and percentiles

NTILE(4) assigns each row to a quartile; PERCENT_RANK() gives a fraction between 0 and 1. MariaDB also has MEDIAN(col) OVER (...) and PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY col) OVER (...), which have no direct portable equivalent and are shown read-only:

SQL
SELECT department_id, salary,
       MEDIAN(salary) OVER (PARTITION BY department_id) AS dept_median,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary)
              OVER (PARTITION BY department_id)        AS dept_p90
FROM employees;
SQL Salary quartiles ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Window or GROUP BY?

Use GROUP BY when you want fewer rows. Use a window function when you want to keep the detail rows and add a computed value. You can combine them: aggregate first in a CTE, then rank the aggregated rows. Window functions run after WHERE, GROUP BY and HAVING, which is why they only appear in SELECT and ORDER BY.

Common mistakes

  • Using a window function in WHERE or HAVING (error): wrap it in a CTE or derived table.
  • Forgetting PARTITION BY and ranking the whole table.
  • Relying on the default frame with LAST_VALUE.
  • Non-deterministic ROW_NUMBER without a unique tie-breaker.
  • Window over a huge unindexed sort: an index matching PARTITION BY + ORDER BY columns avoids a filesort (see EXPLAIN and index design).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Scores are 90, 90, 80. What does DENSE_RANK() return for the 80?

    DENSE_RANK has no gaps after ties (1, 1, 2). RANK would give 3.
  2. 2. Why can't you write WHERE ROW_NUMBER() OVER (...) <= 3?

    Logical order: FROM, WHERE, GROUP BY, HAVING, window functions, SELECT output. Wrap the query to filter on the result.
  3. 3. What does SUM(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) compute?

    The frame has at most three rows: two before the current row and the current row.
  4. 4. Which statement about PARTITION BY is true?

    Partitions are independent windows; unlike GROUP BY, rows are not collapsed.

Summary

  • OVER (PARTITION BY ... ORDER BY ... frame) adds a calculation to each row without collapsing rows.
  • ROW_NUMBER, RANK, DENSE_RANK, NTILE rank rows; LAG/LEAD read neighbours; aggregates with ORDER BY become running totals.
  • Filter window results with a CTE or derived table, and prefer ROWS frames for row-exact behaviour.
  • Named windows (WINDOW w AS (...)) keep queries short.

Next: CTEs and Recursive Queries