LIMIT and OFFSET returning repeating results

There seems to be a bug in my query that causes results to repeat when I increment the OFFSET value. It is supposed to show 20 results per page. Only 3 results match what my query is looking for, but for some reason, some of these results repeat themselves even though they should only appear on the first page of the query results (see below for example).

SELECT all_users_subbed_to.*, 
    (SELECT COUNT(*) 
     FROM subscribers s2 
     WHERE 
     s2.publisher_id = all_users_subbed_to.publisher_id) 
AS subscribers_sub_count
FROM 
    (SELECT publisher_id, 
     subscriber_id, u2.username AS username, 
     u2.user_photo AS user_photo 
     FROM subscribers s 
     INNER JOIN users u 
     ON (u.id = s.subscriber_id) 
     INNER JOIN users u2 
     ON (u2.id = s.publisher_id) 
     WHERE subscriber_id = 80
    ) 
 AS all_users_subbed_to 
 ORDER BY subscribers_sub_count 
 DESC
 LIMIT 20 
 OFFSET 1;

the results of this query are …

| publisher_id | subscriber_id  | username | user_photo | subscribers_sub_count |
|      88      |       80       |  GERPAL1 |  pic.png   |            3          |
|      84      |       80       |  PURPle  |  pic2.png  |            2          |
|      87      |       80       |  Zeeple  |  pic3.png  |            1          |

If I make OFFSET = 2 ie OFFSET 2 the results are …

the results of this query are

| publisher_id | subscriber_id  | username | user_photo | subscribers_sub_count |
|      84      |       80       |  PURPle  |  pic2.png  |            2          |
|      87      |       80       |  Zeeple  |  pic3.png  |            1          |

… Even though these results should only appear when the offset = 1.

If I make OFFSET = 3 ie OFFSET 3 the results are…

| publisher_id | subscriber_id  | username | user_photo | subscribers_sub_count |
|      87      |       80       |  Zeeple  |  pic3.png  |            1          |

… Even though these results should only appear when the offset = 1.

Any idea on how to fix this strange behavior? If not, do you see any bugs in my queries?

Additional info …

users table

| id | username   | user_photo |
| 80 | supercookie| userpic.png|
| 88 | GERPAL1    | pic.png    |
| 84 | PURPle     | pic2.png   |
| 87 | Zeeple     | pic3.png   |

subscribers table

| id | publisher_id | subscriber_id |
| 1  | 88           | 80            |
| 2  | 88           | 84            |
| 3  | 88           | 87            |
| 4  | 84           | 80            |
| 5  | 84           | 88            |
| 6  | 87           | 80            |

Answer

According to PostgreSQL documents offset means OFFSET says to skip that many rows before beginning to return rows.

In your query, you add LIMIT 20 OFFSET 2 this means skip 2 rows from 20 existing rows and two first rows have been skipped and showing duplicate rows of the previous page.

You should use this formula for calculate limit and offset:

LIMIT total_record_show OFFSET total_record_show * (page_number - 1)

--- page 1 (total_record_show: 20, page_number: 1)
LIMIT 20 OFFSET 0

--- page 2 (total_record_show: 20, page_number: 2)
LIMIT 20 OFFSET 20

--- page 3 (total_record_show: 20, page_number: 3)
LIMIT 20 OFFSET 40

Advertisement

Answer