SQL window functions cheat-sheet: ROW_NUMBER, RANK and LAG/LEAD across five engines

ROW_NUMBER, RANK, DENSE_RANK and LAG/LEAD with OVER, PARTITION BY and frames, verified per engine: PostgreSQL, MySQL/MariaDB, Oracle, SQL Server, SQLite.

Author: Łukasz Żochowski

  • #sql
  • #dbeaver
  • #postgresql
  • #mysql
  • #oracle

Window functions are the moment SQL stops feeling like a reporting language and starts feeling like a real one. Ranking rows, comparing each row to the one before it, running totals that don’t collapse your result set — all of it comes from the same small family of functions and one keyword: OVER. The good news is that the core syntax is remarkably portable across engines. The bad news is that the last 10% — the frame clause, null handling, and which version you’re on — differs just enough to burn you.

This cheat-sheet covers ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD with copy-paste SQL for PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite, and it is honest about where the dialects diverge. Every availability claim and every frame-clause quirk below is checked against the official documentation, linked inline.

What are window functions, and when do you reach for them?

A window function computes a value across a set of rows that are somehow related to the current row — its window — without collapsing those rows into one. That is the whole difference from GROUP BY. An aggregate with GROUP BY gives you one row per group; a window function gives you one value per input row while still returning every row.

The shape is always the same:

function(...) OVER (
  [ PARTITION BY ... ]   -- split the rows into independent groups
  [ ORDER BY ... ]       -- define the order within each group
  [ frame_clause ]       -- which rows around the current one count
)

PARTITION BY restarts the calculation for each group (per department, per customer, per day). ORDER BY inside OVER sets the order the function sees — which is what makes ranking and “previous row” meaningful. The frame clause, which most people never touch, is where the dialects fight. We’ll get to it.

Which engines support window functions, and since when?

This is the first thing to pin down, because “my query works” depends on the version far more than people expect.

If you support older installs, that list is your compatibility floor. The rest of this article assumes a version at or above each of those.

How do you number rows: ROW_NUMBER, RANK and DENSE_RANK?

Here is the genuinely good news: the three numbering functions are syntactically identical across all five engines. This exact query runs on PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite with no changes:

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

What differs is the meaning, and getting it wrong is a classic bug:

  • ROW_NUMBER() gives every row a unique number: 1, 2, 3, 4 — even when two rows tie on the ORDER BY value. Which tied row gets the lower number is arbitrary, so add a tie-breaker column to ORDER BY if you need it deterministic.
  • RANK() gives tied rows the same rank and then leaves gaps: 1, 2, 2, 4. Two silver medals, no bronze.
  • DENSE_RANK() gives tied rows the same rank with no gaps: 1, 2, 2, 3.

The “top N per group” pattern falls straight out of this: wrap the query and filter WHERE row_num <= 3. Use ROW_NUMBER for exactly three rows, RANK/DENSE_RANK if you want to keep everyone tied at the cut-off. One caveat worth stating: the pure ranking functions do not accept a frame clause — in SQL Server, for example, a ROWS/RANGE specification is simply ignored for ranking functions even when ORDER BY is present (OVER clause remarks).

How do you look at the previous or next row: LAG and LEAD?

LAG reaches backward to an earlier row; LEAD reaches forward. They are how you compute day-over-day deltas, detect gaps, or compare a value to its neighbour without a self-join. The signature is consistent everywhere — function(value [, offset [, default]]) — and this query is portable across all five engines:

SELECT
  order_date,
  amount,
  LAG(amount,  1, 0) OVER (ORDER BY order_date) AS prev_amount,
  LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount
FROM orders;

offset defaults to 1 (the immediately adjacent row) and default is what you get when you step off the edge of the partition — without it you get NULL, which is often what trips up a “change since yesterday” calculation on the first row.

The one real divergence here is null handling. Oracle lets you add RESPECT NULLS (the default) or IGNORE NULLS, so you can skip over null values when stepping back to find the last real reading (Oracle: LAG):

-- Oracle: step back to the previous NON-NULL amount
LAG(amount) IGNORE NULLS OVER (ORDER BY order_date)

IGNORE NULLS support on LAG/LEAD is uneven across the other engines, so don’t assume that clause ports — if you rely on it, verify it against the engine you’re actually on rather than the one you tested against.

What is the frame clause, and why does it bite?

This is where cross-engine work actually lives. When you write ORDER BY inside OVER but no explicit frame, the engine applies a default frame — and that default is not “all the rows I ordered.” Across PostgreSQL, MySQL, SQL Server and SQLite the default when ORDER BY is present is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

(Confirmed for MySQL, SQL Server, PostgreSQL and SQLite.) The subtle word is RANGE. In RANGE mode, “current row” includes every peer — every row with the same ORDER BY value. So this running total:

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

does something surprising if order_date has duplicates: all the orders on the same date get the same running total — the sum through the end of that whole day — not a row-by-row accumulation. Nine times out of ten the fix is to be explicit and switch to ROWS, which counts physical rows and is supported in every engine that has window functions at all:

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

Three frame facts to keep straight, all verified:

  • GROUPS mode exists in only two of the five. PostgreSQL (from 11) and SQLite (from 3.25.0) support GROUPS, which moves the frame by whole peer groups (PostgreSQL 11; SQLite). MySQL and SQL Server support only ROWS and RANGE — their grammar has no GROUPS at all (MySQL frames; SQL Server OVER).
  • SQL Server’s RANGE is restricted. You cannot use a numeric offset (RANGE BETWEEN 5 PRECEDING ...) with RANGE in SQL Server — only UNBOUNDED and CURRENT ROW. For a numeric window, you must use ROWS (OVER clause limitations).
  • Without ORDER BY, the default frame is the whole partition — every row is a peer of every other, so an aggregate OVER (PARTITION BY ...) with no order returns the group total on every row. That one is actually portable and useful.

What’s the same, and what differs, across the five engines?

The reassuring summary: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, PARTITION BY, ORDER BY and a plain ROWS frame are effectively the same SQL everywhere. If you stick to those, your window queries port cleanly. The sharp edges are narrow and specific:

  • Version floor — MySQL 8.0, MariaDB 10.2, SQLite 3.25.0; nothing works below that.
  • GROUPS mode — PostgreSQL 11+ and SQLite 3.25+ only.
  • RANGE with numeric offsets — not in SQL Server.
  • IGNORE NULLS on LAG/LEAD — Oracle yes; verify elsewhere.
  • The default RANGE frame — the quiet source of wrong running totals when the ORDER BY column has ties.

If this is useful, it pairs directly with the broader dialect differences across these same five engines — pagination, identifier quoting, concatenation — and with reading the execution plan once a window query over a big table turns slow.

How do you stay dialect-aware without memorising all of this?

Keep a reference like this one, and lean on tooling that knows the dialect you’re connected to rather than writing one flavour and leaving you to translate.

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool mentioned below.

SQL Sage for DBeaver is a plugin that adds an AI copilot inside DBeaver and is dialect-aware across these same five engines — PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite — so the window-function syntax it writes matches the connection you’re on, including the frame-clause differences above. A few facts stated plainly: it is Windows-only (Linux and macOS are not supported), it is keyless (you sign in with your own Claude or ChatGPT account, no API key), and it is read-only by default — on PostgreSQL and MySQL/MariaDB that read-only mode is enforced at the engine level, not just in the UI. It is not affiliated with DBeaver, Oracle, Microsoft, Anthropic or OpenAI.

Whether you use a tool or your own memory, the takeaway is the same: the core window-function syntax is portable, and the money is in the frame clause. Bookmark this page for the next time a running total comes back looking wrong.

← Back to the blog