ROWNUM Simulation in MySQL (Legacy Tricks + ROW_NUMBER() Since MySQL 8)

MySQL had no ROWNUM pseudo-column until version 8.0 (released 2018) — migrating from Oracle or SQL Server you had to fake it with user-defined variables. Here’s the classic pre-8 trick for legacy databases plus the modern ROW_NUMBER() window function that makes all of it obsolete.

I wrote the original version of this snippet in 2012 during a migration from Oracle to MySQL 5.5. The user-variable trick was the only way to get row numbering without pulling data into the application layer and numbering in code. MySQL 8.0 finally added window functions in 2018, which is the right answer now — but the older trick still works if you’re stuck on MySQL 5.7 or earlier (which is EOL but still runs on a surprising number of shared-hosting environments).

What this snippet covers

  • Modern (MySQL 8+) — ROW_NUMBER() OVER (ORDER BY col) window function, clean, standard SQL
  • Legacy (MySQL 5.x) — user-defined variable with (@rownum := @rownum + 1) in a cross-join
  • When to rank vs number — ROW_NUMBER() vs RANK() vs DENSE_RANK() distinctions
  • Pagination pattern — why LIMIT/OFFSET on row numbers is faster than on raw queries for deep pagination
  • MariaDB — added window functions in 10.2 (released 2017), same syntax as MySQL 8
  • PostgreSQL and SQLite — both have always had ROW_NUMBER(), no tricks needed

Use it

For any MySQL 8+ or MariaDB 10.2+ database, use ROW_NUMBER(). For older databases still in production, the user-variable trick is the fallback. If your shared host still runs MySQL 5.6 (some do), budget for the upgrade — 5.6 hit end-of-life in February 2021.

-- MODERN: MySQL 8.0+ and MariaDB 10.2+
SELECT
  ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rownum,
  id,
  title,
  created_at
FROM posts
WHERE status = 'publish'
ORDER BY created_at DESC
LIMIT 20;

-- Ranked (ties share the same rank, next rank skips numbers)
SELECT
  RANK() OVER (ORDER BY score DESC) AS rank,
  user_id,
  score
FROM leaderboard;

-- Dense ranked (ties share rank, next rank is always +1)
SELECT
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
  user_id,
  score
FROM leaderboard;

-- Per-group numbering (partitioned window)
SELECT
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank_in_category,
  product_id,
  category_id,
  sales
FROM product_sales;

-- LEGACY: MySQL 5.7 and earlier — user variable trick
SELECT
  @rownum := @rownum + 1 AS rownum,
  p.id,
  p.title,
  p.created_at
FROM posts p
CROSS JOIN (SELECT @rownum := 0) r
WHERE p.status = 'publish'
ORDER BY p.created_at DESC
LIMIT 20;

Why the legacy trick works (and its gotchas)

MySQL user-defined variables persist across statements in a session. The CROSS JOIN (SELECT @rownum := 0) is a trick to initialise the variable in the same statement — otherwise you’d need a separate SET @rownum = 0; statement, which breaks if you run the query through an ORM that only accepts single statements. The @rownum := @rownum + 1 evaluates left-to-right during row processing, incrementing for each row. Gotcha: the order of evaluation of user variables in SELECT is officially undefined in MySQL 5.x — it works reliably when the variable is assigned in a column position that the optimiser processes sequentially, but subqueries and GROUP BY can reorder it unpredictably. ROW_NUMBER() doesn’t have that problem — window functions are standardised and deterministic.

2026 recommendations

  • If you’re on MySQL 8+ or MariaDB 10.2+: always use ROW_NUMBER(), never the user-variable trick
  • If you’re on MySQL 5.7: plan a migration. 5.7 hit EOL in October 2023, security patches only until 2024
  • If you’re on MySQL 5.6: migrate immediately — EOL since February 2021, known CVEs unpatched
  • For shared hosting that won’t upgrade: move to a host that runs MySQL 8 or MariaDB 10.6+. My pick in 2026 is Cloudways or Rocket.net
  • Official MySQL docs: dev.mysql.com/doc/refman/8.0/en/window-functions.html

FAQs

Why didn’t MySQL have ROW_NUMBER() earlier?

MySQL’s development pre-Oracle-acquisition (before 2010) prioritised speed over SQL-standard completeness. Window functions are expensive to implement correctly. They finally shipped in 8.0 after Oracle invested in the engine. MariaDB beat them to it by a year (10.2, 2017) because the MariaDB fork was actively adding SQL-standard features.

Is the user-variable trick fully deprecated?

Officially discouraged — the MySQL 8 docs warn that SET @var = ... within a query expression is deprecated behaviour. For MySQL 8 code, always use window functions. The trick still works on MySQL 8 for backward compatibility but will be removed in a future major version.

What about ROW_NUMBER() performance on large tables?

Window functions are optimised in MySQL 8 and use in-memory sorting by default. For tables over 10 million rows, the sort can spill to disk — solve with an index on the ORDER BY column that matches the window’s ORDER BY, which lets the optimiser skip the sort entirely.

Can I use ROW_NUMBER() for pagination?

Yes and it’s the modern best practice for deep pagination. LIMIT 1000000, 20 forces the engine to scan the first million rows before returning 20 — slow. WHERE row_num BETWEEN 1000000 AND 1000020 uses the computed column which is faster if indexed. For even deeper pagination, switch to keyset pagination (WHERE id < last_seen_id ORDER BY id DESC LIMIT 20).

What about PostgreSQL?

PostgreSQL has had ROW_NUMBER() since version 8.4 (released 2009). If you’re on PostgreSQL, you’ve never had this problem.

Does this work on AWS Aurora MySQL?

Yes. Aurora MySQL 3.x is MySQL 8-compatible and supports all window functions. Aurora MySQL 2.x (5.7-compatible) needs the legacy trick.

Disclaimer: This site is reader-supported. If you buy through some links, I may earn a small commission at no extra cost to you. I only recommend tools I trust and would use myself. Your support helps keep gauravtiwari.org free and focused on real-world advice. Thanks. - Gaurav Tiwari

Leave a Comment