Duplicate records on joining I have two tables books and books_data. books can have multiple books_data, books_data can have only one book. books_data table has date column by which I want to sort the books table. When trying to do this, the code returns duplicates. But the duplicates appear only when trying to paginate results, e.g. if I provide LIMIT 10 OFFSET 0 for first query and LIMIT 10 OFFSET 20 for the second, some of the records from first query will appear in the second query too. If I specify LIMIT 20 OFFSET 0 there are no duplicates. Here's my query (it may be not perfect, because it is generated by Rails ORM Active Record):

SELECT DISTINCT "books"."id", "books_data"."date" FROM "books"
LEFT OUTER JOIN "books_data" ON "books_data"."book_id" = "books"."id"
ORDER BY books_data.date DESC LIMIT 10 OFFSET 0;
Try adding books.id to ORDER BY. If books_data.date is not unique, relative order of rows in resultest is not guaranteed. Say you have 5 rows with date1, 10 rows with date2, and 5 with date3; date1 &lt; date2 &lt; date3.  

Then some rows with date2 may appear on both pages, some on neither, and , if you are lucky enough, you may sometimes see expected results.
Assuming (books.id,books_data.date ) unique the following should return consistent results:

SELECT DISTINCT "books"."id", "books_data"."date" FROM "books"
LEFT OUTER JOIN "books_data" ON "books_data"."book_id" = "books"."id"
ORDER BY books_data.date DESC, books.id ASC LIMIT 10 OFFSET 0;


Short illustration : 


id | some_non_unique_data  
1  |  'val1'  
2  |  'val2'  
3  |  'val2'


When splitting such data into 2 pages, 2 rows per page ordering only by some_non_unique_data ascending the engine is free to return row id=2(or id=3) on any page. emphasized text