Pagination - The Definitive Guide

Pagination - EASY you say: just use LIMIT, OFFSET syntax. But what happens if I want a paginated list of Authors with a list of Books by each Author next to it?

 SELECT author_name,book_title FROM author INNER JOIN book USING(author_id) LIMIT 10; 

Now imagine that you have 5 Authors in your database each with 20 books. That query will return 10 rows, all with the same Author in them.

So how do you paginate such a query, and hence any joined query? It's a matter of using 3 separate queries (2 if the RDBMS you are using supports LIMIT,OFFSET syntax in subselects).

SELECT count(author_id) AS total FROM author 
    INNER JOIN book USING(author_id);
id_list = SELECT author_id FROM author LIMIT 10;
SELECT author_name,book_title FROM author INNER JOIN 
    book USING(author_id) WHERE author_id IN(id_list)

If your RDBMS supports LIMIT,OFFSET syntax in subselects, then steps 2 and 3 can be done with:

SELECT author_name,book_title FROM author 
    INNER JOIN book USING(author_id) WHERE author_id 
    IN SELECT author_id FROM author LIMIT 10;

You can then use whatever programming language you like to go to a page number like this (example in PHP):

$num_pages = floor($count/$num_per_page);
if($count%$num_per_page)
    $num_pages++;
if($num>$num_pages)
    throw new Exception('Page: '.$num.' does not exist!');
$start = (($num-1)*$num_per_page); 

Then use $start as the OFFSET and $num_per_page as the LIMIT Happy paginating!