http://oncodesign.io/2017/04/22/paging-one-to-many-results-in-sql

dense-rank-offset-0-count-2-wrapped.sql

SELECT * FROM 
  (SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM 
     (SELECT *, DENSE_RANK() OVER (ORDER BY name, id) offset_ FROM
        (SELECT a.id, a.name, b.title 
         FROM AUTHOR a, BOOK b 
         WHERE a.id = b.authorId) result) result_offset
      WHERE offset_ > 0) result_offset_count 
WHERE count <= 2

dense-rank-offset-0-count-2.sql

SELECT * FROM 
  (SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM 
     (SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_ 
      FROM AUTHOR a, BOOK b 
      WHERE a.id = b.authorId) result_offset 
   WHERE offset_ > 0) result_offset_count 
WHERE count <= 2

dense-rank-offset-0-offset-2.sql

SELECT * FROM 
  (SELECT *, DENSE_RANK() OVER (ORDER BY name, id) offset_ FROM
    (SELECT a.id, a.name, b.title 
     FROM AUTHOR a, BOOK b 
     WHERE a.id = b.authorId) result) result_offset
WHERE offset_ > 0 AND offset_ <= (0 + 2)

dense-rank-offset-1-count.sql

SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM 
  (SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_ 
   FROM AUTHOR a, BOOK b 
   WHERE a.id = b.authorId) result_offset 
WHERE offset_ > 1

dense-rank-offset-1.sql

SELECT * FROM 
  (SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_ 
   FROM AUTHOR a, BOOK b 
   WHERE a.id = b.authorId) result_offset 
WHERE offset_ > 1

dense-rank-offset.sql

SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_ 
FROM AUTHOR a, BOOK b 
WHERE a.id = b.authorId

limit-offset.sql

SELECT a.id, a.name, b.title 
FROM AUTHOR a, BOOK b 
WHERE a.id = b.authorId 
ORDER BY a.name LIMIT 2 OFFSET 0

result-1.txt

 ID  |         Name         |            Title
 21  | Hintjens Peter       | Confessions of a Necromancer
 21  | Hintjens Peter       | Social Architecture
 21  | Hintjens Peter       | ZeroMQ, Messaging for Many Applications
 89  | Chomsky Noam         | How the World Works
 89  | Chomsky Noam         | The Architecture of Language
 89  | Chomsky Noam         | What Kind of Creatures are We?
 144 | Antonopoulos Andreas | The Internet of Money
 144 | Antonopoulos Andreas | Mastering Bitcoin
 144 | Antonopoulos Andreas | Mastering Ethereum

result-2.txt

 ID  |         Name         |            Title
 144 | Antonopoulos Andreas | The Internet of Money
 144 | Antonopoulos Andreas | Mastering Bitcoin

result-3.txt

 ID  |         Name         |            Title                         | offset_
 144 | Antonopoulos Andreas | The Internet of Money                    |   1
 144 | Antonopoulos Andreas | Mastering Bitcoin                        |   1
 144 | Antonopoulos Andreas | Mastering Ethereum                       |   1
 89  | Chomsky Noam         | How the World Works                      |   2
 89  | Chomsky Noam         | The Architecture of Language             |   2
 89  | Chomsky Noam         | What Kind of Creatures are We?           |   2
 22  | Hintjens Peter       | Confessions of a Necromancer             |   3
 22  | Hintjens Peter       | Social Architecture                      |   3
 22  | Hintjens Peter       | ZeroMQ, Messaging for Many Applications  |   3

result-4.txt

 ID |     Name       |                  Title                   | offset_
 89 | Chomsky Noam   | How the World Works                      |   2
 89 | Chomsky Noam   | The Architecture of Language             |   2
 89 | Chomsky Noam   | What Kind of Creatures are We?           |   2
 22 | Hintjens Peter | Confessions of a Necromancer             |   3
 22 | Hintjens Peter | Social Architecture                      |   3
 22 | Hintjens Peter | ZeroMQ, Messaging for Many Applications  |   3

result-5.txt

 ID |     Name       |                  Title                   | offset_ | count
 89 | Chomsky Noam   | How the World Works                      |   2     |   1
 89 | Chomsky Noam   | The Architecture of Language             |   2     |   1
 89 | Chomsky Noam   | What Kind of Creatures are We?           |   2     |   1
 22 | Hintjens Peter | Confessions of a Necromancer             |   3     |   2
 22 | Hintjens Peter | Social Architecture                      |   3     |   2
 22 | Hintjens Peter | ZeroMQ, Messaging for Many Applications  |   3     |   2

result-6.txt

 ID  |     Name              |                  Title         | offset_ | count
 144 | Antonopoulos Andreas  | The Internet of Money          |    1    |   1
 144 | Antonopoulos Andreas  | Mastering Bitcoin              |    1    |   1
 144 | Antonopoulos Andreas  | Mastering Ethereum             |    1    |   1
 89  | Chomsky Noam          | How the World Works            |    2    |   2
 89  | Chomsky Noam          | The Architecture of Language   |    2    |   2
 89  | Chomsky Noam          | What Kind of Creatures are We? |    2    |   2

schema.sql

CREATE TABLE AUTHOR
(
   id    INTEGER PRIMARY KEY,
   name  VARCHAR(255)
);

CREATE TABLE BOOK
(
   id        INTEGER PRIMARY KEY,
   authorId  INTEGER REFERENCES Author (id),
   title     VARCHAR(255)
);

sub-select.sql

SELECT a.id, a.name, b.title 
FROM (SELECT * FROM AUTHOR ORDER BY name LIMIT 2 OFFSET 0) a, BOOK b 
WHERE a.id = b.authorId