Top-N-Per-Group in MySQL: LATERAL Derived Tables for PHP Developers

Almost every application I have worked on eventually asks the same question in some form. Give me each author and their three most recent articles. Give me each customer and their last five orders. Give me each product category and its top-selling items. It sounds trivial until you sit down to write the query, and then you discover that “the top N rows within each group” is one of the genuinely awkward problems in SQL.

Most PHP developers solve it the way that feels natural in application code. You fetch the parent rows, loop over them, and run a second query inside the loop to grab the children. It works, it reads clearly, and it quietly issues one query per parent. Ten authors on a page is eleven queries. A hundred authors is a hundred and one. This is the classic N+1 problem, and no amount of clever PHP makes the round trips disappear. The fix belongs in the database, and since MySQL 8.0.14 there has been a clean tool for it: the LATERAL derived table.

What LATERAL actually does

A normal derived table, the subquery you put in a FROM clause, is not allowed to reference columns from the other tables sitting next to it in that same FROM clause. It has to be constant for the duration of the query. That restriction is exactly what stops you from writing the query you want, because a “top three articles for this author” subquery needs to know which author it is looking at.

The LATERAL keyword lifts that restriction. As the MySQL manual puts it, a lateral derived table is brought up to date each time a new row from a preceding table is processed. In plain terms, the subquery runs once per outer row and can see that outer row’s columns. That is precisely the shape of a top-N-per-group query, and it maps directly onto how you were already thinking about the problem in your PHP loop, except the loop now happens inside MySQL in a single statement.

The approaches you have probably tried

Before LATERAL, there were two common workarounds, and both have rough edges.

The correlated subquery approach puts one subquery per column you want in the SELECT list. If you need both the article title and its publish date, you end up running the subquery twice, and MySQL recomputes the same lookup for each column. It is verbose and it wastes work.

The window function approach is cleaner. You use ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY published_at DESC) in a subquery, then filter the outer query to WHERE rn <= 3. This is a solid technique and it is worth knowing. The catch is that it computes row numbers across the entire table before throwing most of them away, so on a large table it can do far more work than you want just to keep three rows per author.

LATERAL sidesteps both problems. It fetches exactly the rows you asked for, once, and it lets each per-group subquery use LIMIT the way you would expect.

The query

Here is the pattern using an authors and articles schema, the kind of thing you might find behind the PHP Architect article catalog.

SELECT
    authors.name,
    latest.title,
    latest.published_at
FROM
    authors,
    LATERAL (
        SELECT title, published_at
        FROM articles
        WHERE articles.author_id = authors.id
        ORDER BY published_at DESC
        LIMIT 3
    ) AS latest;

Read it top to bottom and it says what you mean. For each author, pull their three newest articles. The LATERAL keyword is the whole trick: without it, MySQL rejects the reference to authors.id with ERROR 1054 (42S22): Unknown column 'authors.id' in 'where clause'. With it, the subquery is re-evaluated per author and each evaluation returns at most three rows.

One important detail. Using the comma join above gives you an inner-join behavior, so authors with no articles drop out of the result. If you want every author to appear, including those who have not published yet, switch to an explicit LEFT JOIN with an ON TRUE condition:

SELECT authors.name, latest.title, latest.published_at
FROM authors
LEFT JOIN LATERAL (
    SELECT title, published_at
    FROM articles
    WHERE articles.author_id = authors.id
    ORDER BY published_at DESC
    LIMIT 3
) AS latest ON TRUE;

The MySQL manual spells out the restriction that makes this necessary: when a lateral derived table sits on the right side of a join and references the left side, the join has to be an INNER JOIN, CROSS JOIN, or LEFT JOIN. A RIGHT JOIN in that direction is not allowed.

Calling it from PHP

There is nothing special about running this over PDO. It is a plain statement, so prepared parameters work exactly as they would anywhere else.

$pdo = new PDO('mysql:host=localhost;dbname=phparch', $user, $pass);

$sql = <<<SQL
    SELECT authors.name, latest.title, latest.published_at
    FROM authors
    LEFT JOIN LATERAL (
        SELECT title, published_at
        FROM articles
        WHERE articles.author_id = authors.id
          AND articles.status = :status
        ORDER BY published_at DESC
        LIMIT 3
    ) AS latest ON TRUE
    ORDER BY authors.name;
SQL;

$stmt = $pdo->prepare($sql);
$stmt->execute(['status' => 'published']);

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    // one flat result set, zero extra round trips
}

That single query replaces the fetch-then-loop pattern that would otherwise fire a query per author.

The Laravel way

If you are on Laravel, you do not have to drop to raw SQL. The query builder has joinLateral and leftJoinLateral, and the Laravel documentation notes they are supported on PostgreSQL, MySQL 8.0.14 and newer, and SQL Server. You build the subquery, correlate it with whereColumn, and hand it to the builder with an alias.

use Illuminate\Support\Facades\DB;

$latestArticles = DB::table('articles')
    ->select('title', 'published_at')
    ->whereColumn('author_id', 'authors.id')
    ->where('status', 'published')
    ->orderByDesc('published_at')
    ->limit(3);

$authors = DB::table('authors')
    ->leftJoinLateral($latestArticles, 'latest')
    ->orderBy('authors.name')
    ->get();

The whereColumn('author_id', 'authors.id') call is what ties the subquery to the outer row, and the builder generates the LATERAL join for you.

A few things to watch

Index the correlation column. Since the subquery runs once per outer row, you want each run to be a fast index lookup. A composite index on articles (author_id, published_at) lets MySQL find and order each author’s articles without a filesort. Confirm it with EXPLAIN before you ship.

Mind your database engine. If you deploy to MariaDB rather than MySQL, be aware that lateral derived tables are a MySQL feature. That is why the Laravel documentation lists PostgreSQL, MySQL, and SQL Server but not MariaDB. On MariaDB you will need the window function approach instead.

Finally, note that MySQL already treats JSON_TABLE() as implicitly lateral, so you never write the LATERAL keyword in front of it. That is per the SQL standard, and MySQL follows it.

LATERAL derived tables are not new, but they are still underused in PHP codebases where the N+1 loop has simply become muscle memory. The next time you catch yourself querying inside a foreach, remember that MySQL can do that loop for you in one statement, and it will almost always do it faster.