I’m using a tool that generates the Postgresql query below:
SELECT "id", "score" FROM "players" WHERE "score" > '11266' OR ( "score" = '11266' AND "id" > '4482' ) ORDER BY "score" ASC, "id" ASC LIMIT 3
I need to understand why the
My players table can have many rows with the same
score but not the same
OR needed when multiple rows has the same
The purpose of the
OR — as you suspect — is to handle the case where there are ties in the scores. The idea is to make a stable sort by including the
id, so this this getting everything after
Presumably, the values used for
id are the last values seen (probably on the previous page, but that is speculation).
A “stable” sort is one that returns the rows in the same order each time it is applied. Because SQL tables represent unordered sets, ties imply an unstable sort. Including the
id makes it stable (assuming that
id is unique.
Postgres actually supports a simper syntax:
where (score, id) > (11266, 4482)
Note that I also removed the single quotes. The values look like numbers so they should be treated as numbers not strings.