How can I sort SQLite results predictably when values tie?
Quick answer
Add another expression to ORDER BY to specify how rows tied on the first expression should be ordered. For example, ORDER BY score DESC, player_id ASC sorts scores from highest to lowest, then IDs from lowest to highest within each tied score. The secondary expression should distinguish the rows whose order matters; rows tied on every listed expression still have no specified relative order. [S1]
The question, the details, the next step.
I query a SQLite score table with ORDER BY score DESC, but players with the same score sometimes appear in a different order. How do I make that order predictable?
Add a second sort expression that says how to order players with equal scores. SQLite applies ORDER BY expressions in sequence: the first sets the main order, and the next breaks ties from the earlier expression. If rows tie on every expression you list, their relative order is undefined. [S1]
Suppose this invented table contains:
player_id | name | score
14 | Mira | 90
8 | Leon | 90
21 | Noor | 75
With ORDER BY score DESC, the two 90-point rows tie on the only requested sort value. The query specifies that the 90-point scores come before 75, but not which of the tied rows comes first. [S1]
What should the before-and-after queries and results look like for that example?
Before, the query is:
SELECT player_id, name, score FROM players ORDER BY score DESC;
The two 90-point rows could appear in either order, followed by Noor. That does not mean SQLite will alternate between orders; the query leaves the relative order of equal scores unspecified. [S1]
After, add the ID as a tie-breaker:
SELECT player_id, name, score FROM players ORDER BY score DESC, player_id ASC;
The specified order for these invented rows is:
8 | Leon | 90
14 | Mira | 90
21 | Noor | 75
Scores are descending; within equal scores, IDs are ascending. This result assumes player_id differs for the tied rows. It illustrates the ordering rule, not a report of a real database or test. [S1]
Does the second column have to be unique, and how do I choose its direction?
It does not have to be unique across the whole table, but it must distinguish the rows whose relative order you want to specify. If two tied-score rows also have the same player_id, those expressions still leave the rows tied. Add another suitable expression if you need to order them relative to each other. Rows tied on all listed expressions have an undefined relative order. [S1]
Choose ASC or DESC to match your intended tie rule. In this example, player_id ASC puts the smaller ID first among equal scores; player_id DESC would put the larger ID first. The second expression does not change the primary score order: scores remain descending.
Can I use this as a quick review checklist for a real query?
Yes. Write down the order you want in plain language, then check that each part appears in the ORDER BY list. For example: “highest score first; for ties, smaller player ID first” maps to ORDER BY score DESC, player_id ASC.
Ask yourself: what is the primary sort value, what should happen when it ties, and does the next expression distinguish the tied rows? If not, decide whether another tie-breaker is needed. The right columns depend on the ordering rule you want for your data.
A practical checklist
State the primary ordering rule, such as highest score first.
Choose a secondary expression that defines the order of rows tied on the primary value.
Set each expression’s direction to match the intended order.
Check whether the expressions distinguish every tied row whose relative order matters.
Use an example to review the requested sequence.
A few more useful answers
If I omit the secondary expression, will SQLite sort ties by row ID automatically?
Do not rely on an unstated tie-breaker. The relative order of rows tied on every listed ORDER BY expression is undefined, so include the ordering rule you need. [S1]
What if my secondary column also has duplicate values?
Add another expression that distinguishes those rows if their relative order matters. Rows tied on every listed expression have no specified relative order. [S1]
Can I use different directions for the two sort rules?
Yes. ORDER BY score DESC, player_id ASC means scores descend, while IDs ascend within equal scores.
Sources
References used for this guide.
- [S1] SQLite SELECT — ORDER BY sorting and ties sqlite.org · Checked