CODEWITHSUNDEEP
databasepostgresqlselectsql-order-bypagination
Hossein.Kiani
Hossein.Kiani

Reputation: 137

Repeating some rows in some offsets in the PostgreSQL

Why sometimes Am I getting repeated row in different offsets when I'm using select, join, order by, offset & limit together?

select * from users u
left join posts p on p.user_id = u.id
order by u.id
offset 0, limit 20

Upvotes: 1

Views: 768

Answers (1)

GMB
GMB

Reputation: 222592

The problem is that you are sorting with a column (amount) that contains duplicate values. Your order by clause is not deterministic, hence the results are not stable.

A simple solution is to usea second sorting criteria in order to break the ties (it looks like user(id) can do this):

select *                                  -- better enumerate the columns here
from wallets w
inner join users u on u.id = w.user_id    -- your "left join" is actually "inner join"
where u.role = 'tester' and w.amount > 0
order by w.amount, u.id                   -- here is the second sorting criteria
offset 0, limit 20

Upvotes: 3

Related Questions