SQL query for pagination with multiple columns; understand OR operator

user_10088259 0
Viewed 922

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 OR operator?

My players table can have many rows with the same score but not the same id.

Is that OR needed when multiple rows has the same score value?

postgresql sorting sql database
1 Answers

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 (score, id).

Presumably, the values used for score and 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.

user_11440350
Related