title: SQL statement with pagination and sorting (join tables)
body: <p>I have two tables (<strong>Books, Authors</strong>) with <strong><em>ManyToMany</em></strong> relationship. <strong>I need SQL statement</strong> to retrieve <em>books with authors</em>, <em>sorted by authors/books</em>. Important thing is that I must retrieve them <em>with pagination</em> (offset ... fetch in sql).
  One of the problem that when I join tables there are duplicates in results (of course) and offset/fetch can't be used for this results. Another problem the results must be sorted (not subpages but all books of course).</p>
  
  <p>I have one idea: (it retrieves books sorted by author name and include pagination)</p>
  
  <pre><code>select  b.id, b.title, a.name from Books b inner join Books_Authors ba
                            on ba.bookID = b.id inner join Authors a
                            on ba.authorID = a.id     
                            where a.name in (select name from Authors order by name offset 9 rows fetch next 3  rows only)
                            order by a.name
  </code></pre>
  
  <p>But I think it's not efficient way.</p>