Top-N Sorts: Limit and Pagination

Explore how to retrieve top-N results by category using lateral joins and CTEs in PostgreSQL. Learn why offset in pagination harms performance and how to use index lookups and row constructs for efficient data paging. This lesson helps you master advanced query techniques to optimize output and handle large datasets effectively.

We'll cover the following...

No offset
How to implement pagination
Try it yourself

PostgreSQL

with decades as
(
   select extract('year' from date_trunc('decade', date)) as decade
     from races
 group by decade
)
select decade,
       rank() over(partition by decade
                   order by wins desc)
       as rank,
       forename, surname, wins
  from decades
       left join lateral
       (
          select code, forename, surname, count(*) as wins
            from drivers
                 join results
                   on results.driverid = drivers.driverid
                  and results.position = 1
                 join races using(raceid)
           where   extract('year' from date_trunc('decade', races.date))
                 = decades.decade
        group by decades.decade, drivers.driverid
        order by wins desc
           limit 3
       )
       as winners on true
order by decade asc, wins desc;

The above query is a classic top-N implementation. It reports for each decade the top three drivers in terms of race wins. It’s simultaneously a classic top-N implementation because it’s done thanks to a lateral subquery, and a not-so-classic implementation because we’re joining against computed data. The decade information is not part of our data model, and we need to extract it from the races.date column.

The query extracts the decade first in a common table expression introduced with the with keyword. This CTE is then reused as a data source in the from clause. The from clause is about relations, which might be hosting a dynamically computed dataset, as is the case in this example. ...