{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"sorting-limits-and-ties","title":"Sorting, limits, and the rows that move between runs","url":"https://addaly.com/learn/data-and-sql/sorting-limits-and-ties","position":7,"of":66,"minutes":7,"answer":"Without ORDER BY a result has no order, and with ORDER BY on a column that ties it has no order among the ties.","keyIdea":"Without ORDER BY a result has no order, and with ORDER BY on a column that ties it has no order among the ties; add a unique column as the final sort key before you paginate."},"body":{"format":"markdown","text":"## A query without ORDER BY has no order\n\nThis surprises people, so state it plainly: unless you write `ORDER BY`, the database is free to return rows in any order it likes, and it will change its mind. The same query can come back in a different order after an index is added, after rows are updated, or when the planner decides to use several CPU cores.\n\nIt usually looks stable, which is what makes it dangerous. A report that has been fine for eight months starts showing a different \"top 10\" because someone ran a maintenance job.\n\n```sql\nSELECT full_name FROM students LIMIT 10;            -- ten arbitrary students\nSELECT full_name FROM students ORDER BY id LIMIT 10; -- the same ten, always\n```\n\n`LIMIT` without `ORDER BY` does not mean \"the first ten\". It means \"any ten, stop early\".\n\n## Ties do the same thing at a smaller scale\n\nNow the subtler version. You sort by a column with repeated values:\n\n```sql\nSELECT id, full_name, class_level\nFROM students\nORDER BY class_level DESC\nLIMIT 20;\n```\n\nIf forty students are in class 10, the twenty you get are an arbitrary twenty of the forty, and re-running may hand you a different twenty. The rule that fixes it is short: **always end ORDER BY with something unique.**\n\n```sql\nORDER BY class_level DESC, id\n```\n\nNow the ordering is total. Nothing can move. Paginating a list without a unique tiebreaker is the cause of the classic bug where a row appears on both page 1 and page 2, and another row appears on neither.\n\n## Where NULLs sort\n\nAscending in Postgres, NULLs come last. Descending, they come first. Other databases disagree: MySQL puts NULLs first when ascending. So a \"worst performers\" report ordered `ORDER BY score DESC` may open with every student who has no score at all.\n\nSay what you want:\n\n```sql\nORDER BY score DESC NULLS LAST\n```\n\nSQLite and MySQL lack that clause; the portable trick is to sort on a flag first:\n\n```sql\nORDER BY (score IS NULL), score DESC\n```\n\n`false` sorts before `true`, so rows with a score come first.\n\n## Sorting text is not alphabetical\n\n`ORDER BY full_name` sorts by the database's *collation*, and collations disagree about real questions. Whether uppercase sorts before lowercase. Whether `Ångström` files under A or after Z. Whether `ä` equals `a`. Whether accents are ignored. A Postgres cluster initialised with the C locale sorts by raw byte value, which puts every capital letter before every lowercase one, so `Zara` precedes `ade`.\n\nThat is not broken, it is a different definition of order. If a person will read the list, ask for a human collation explicitly:\n\n```sql\nSELECT full_name FROM students ORDER BY full_name COLLATE \"en_US.utf8\";\n```\n\nAnd be aware of the operational side: a collation change during an operating system upgrade can silently invalidate text indexes, because the index was built in one order and the database now believes in another. This has caused real corruption incidents. It is one of the few database problems that a completely correct query cannot protect you from.\n\n## Sorting by something you computed\n\nYou can order by an alias or by position, and one of those is a trap:\n\n```sql\nSELECT city, count(*) AS orders\nFROM orders GROUP BY city\nORDER BY orders DESC;     -- fine, clear\n\nORDER BY 2 DESC;          -- works, but breaks the day someone adds a column\n```\n\nOrdinals are convenient in a throwaway query and a liability in anything saved.\n\n## DISTINCT is a reshaping, not a filter\n\n`SELECT DISTINCT city FROM customers` returns each city once. Two things worth knowing.\n\nFirst, DISTINCT applies to the *whole* selected row, not the first column. `SELECT DISTINCT city, id FROM customers` returns every row, because the id makes each one unique. People write this expecting one row per city and get the full table.\n\nSecond, DISTINCT is often a sign that a join is multiplying rows. The instinct to add it to make a count look right is the instinct to hide the fault rather than find it. If a `SELECT DISTINCT` fixes your row count, go back and ask which join matched twice: the DISTINCT will also have quietly merged different rows that happened to look alike.\n\n## The habit\n\nTwo rules that cost nothing and save whole afternoons:\n\n- Anything with `LIMIT` gets an `ORDER BY`.\n- Any `ORDER BY` ends with a unique column.\n\nThen a query returns the same rows today, tomorrow and after the database is upgraded, which is the minimum standard for a number anybody is going to act on."},"check":{"question":"A results page runs `ORDER BY created_at LIMIT 20 OFFSET 20`, and hundreds of rows share the same `created_at` because they were bulk-imported. A user reports that some items appear on page 1 and again on page 2, while others never appear at all. What is happening?","options":[{"text":"Rows created between the two page requests shift everything down by one position, so page 2 repeats the last row from page 1 and hides another.","correct":false,"tempting":"This is a real problem with offset pagination, but it happens with brand new rows, not with the ties described here."},{"text":"Nothing, as long as `created_at` has an index, because the index fixes the order of equal values.","correct":false,"tempting":"Assumes an index imposes a stable secondary order; the planner may not use that index, and equal keys have no guaranteed order within it."},{"text":"Rows sharing a timestamp may come back in a different order per request, so a tied row can land on both pages or on neither.","correct":true},{"text":"`OFFSET` skips rows before sorting, so the twenty skipped rows are not the twenty shown on page 1.","correct":false,"tempting":"Misstates the order of operations: OFFSET is applied after ORDER BY, not before."}]},"prev":{"slug":"select-and-where","title":"SELECT and WHERE: asking for rows","url":"https://addaly.com/learn/data-and-sql/select-and-where"},"next":{"slug":"case-expressions","title":"CASE, COALESCE and computing a column that does not exist","url":"https://addaly.com/learn/data-and-sql/case-expressions"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Sorting, limits, and the rows that move between runs', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/sorting-limits-and-ties (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Sorting, limits, and the rows that move between runs","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"Without ORDER BY a result has no order, and with ORDER BY on a column that ties it has no order among the ties; add a unique column as the final sort key before you paginate.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"a-query-without-order-by-has-no-order","runs":[{"text":"A query without ORDER BY has no order"}]},{"t":"p","runs":[{"text":"This surprises people, so state it plainly: unless you write "},{"text":"ORDER BY","code":true},{"text":", the database is free to return rows in any order it likes, and it will change its mind. The same query can come back in a different order after an index is added, after rows are updated, or when the planner decides to use several CPU cores."}]},{"t":"p","runs":[{"text":"It usually looks stable, which is what makes it dangerous. A report that has been fine for eight months starts showing a different “top 10” because someone ran a maintenance job."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT full_name FROM students LIMIT 10;            -- ten arbitrary students\nSELECT full_name FROM students ORDER BY id LIMIT 10; -- the same ten, always"},{"t":"p","runs":[{"text":"LIMIT","code":true},{"text":" without "},{"text":"ORDER BY","code":true},{"text":" does not mean “the first ten”. It means “any ten, stop early”."}]},{"t":"h2","id":"ties-do-the-same-thing-at-a-smaller-scale","runs":[{"text":"Ties do the same thing at a smaller scale"}]},{"t":"p","runs":[{"text":"Now the subtler version. You sort by a column with repeated values:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT id, full_name, class_level\nFROM students\nORDER BY class_level DESC\nLIMIT 20;"},{"t":"p","runs":[{"text":"If forty students are in class 10, the twenty you get are an arbitrary twenty of the forty, and re-running may hand you a different twenty. The rule that fixes it is short: "},{"text":"always end ORDER BY with something unique.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"ORDER BY class_level DESC, id"},{"t":"p","runs":[{"text":"Now the ordering is total. Nothing can move. Paginating a list without a unique tiebreaker is the cause of the classic bug where a row appears on both page 1 and page 2, and another row appears on neither."}]},{"t":"h2","id":"where-nulls-sort","runs":[{"text":"Where NULLs sort"}]},{"t":"p","runs":[{"text":"Ascending in Postgres, NULLs come last. Descending, they come first. Other databases disagree: MySQL puts NULLs first when ascending. So a “worst performers” report ordered "},{"text":"ORDER BY score DESC","code":true},{"text":" may open with every student who has no score at all."}]},{"t":"p","runs":[{"text":"Say what you want:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ORDER BY score DESC NULLS LAST"},{"t":"p","runs":[{"text":"SQLite and MySQL lack that clause; the portable trick is to sort on a flag first:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ORDER BY (score IS NULL), score DESC"},{"t":"p","runs":[{"text":"false","code":true},{"text":" sorts before "},{"text":"true","code":true},{"text":", so rows with a score come first."}]},{"t":"h2","id":"sorting-text-is-not-alphabetical","runs":[{"text":"Sorting text is not alphabetical"}]},{"t":"p","runs":[{"text":"ORDER BY full_name","code":true},{"text":" sorts by the database’s "},{"text":"collation","i":true},{"text":", and collations disagree about real questions. Whether uppercase sorts before lowercase. Whether "},{"text":"Ångström","code":true},{"text":" files under A or after Z. Whether "},{"text":"ä","code":true},{"text":" equals "},{"text":"a","code":true},{"text":". Whether accents are ignored. A Postgres cluster initialised with the C locale sorts by raw byte value, which puts every capital letter before every lowercase one, so "},{"text":"Zara","code":true},{"text":" precedes "},{"text":"ade","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"That is not broken, it is a different definition of order. If a person will read the list, ask for a human collation explicitly:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT full_name FROM students ORDER BY full_name COLLATE \"en_US.utf8\";"},{"t":"p","runs":[{"text":"And be aware of the operational side: a collation change during an operating system upgrade can silently invalidate text indexes, because the index was built in one order and the database now believes in another. This has caused real corruption incidents. It is one of the few database problems that a completely correct query cannot protect you from."}]},{"t":"h2","id":"sorting-by-something-you-computed","runs":[{"text":"Sorting by something you computed"}]},{"t":"p","runs":[{"text":"You can order by an alias or by position, and one of those is a trap:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, count(*) AS orders\nFROM orders GROUP BY city\nORDER BY orders DESC;     -- fine, clear\n\nORDER BY 2 DESC;          -- works, but breaks the day someone adds a column"},{"t":"p","runs":[{"text":"Ordinals are convenient in a throwaway query and a liability in anything saved."}]},{"t":"h2","id":"distinct-is-a-reshaping-not-a-filter","runs":[{"text":"DISTINCT is a reshaping, not a filter"}]},{"t":"p","runs":[{"text":"SELECT DISTINCT city FROM customers","code":true},{"text":" returns each city once. Two things worth knowing."}]},{"t":"p","runs":[{"text":"First, DISTINCT applies to the "},{"text":"whole","i":true},{"text":" selected row, not the first column. "},{"text":"SELECT DISTINCT city, id FROM customers","code":true},{"text":" returns every row, because the id makes each one unique. People write this expecting one row per city and get the full table."}]},{"t":"p","runs":[{"text":"Second, DISTINCT is often a sign that a join is multiplying rows. The instinct to add it to make a count look right is the instinct to hide the fault rather than find it. If a "},{"text":"SELECT DISTINCT","code":true},{"text":" fixes your row count, go back and ask which join matched twice: the DISTINCT will also have quietly merged different rows that happened to look alike."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Two rules that cost nothing and save whole afternoons:"}]},{"t":"ul","items":[[{"text":"Anything with "},{"text":"LIMIT","code":true},{"text":" gets an "},{"text":"ORDER BY","code":true},{"text":"."}],[{"text":"Any "},{"text":"ORDER BY","code":true},{"text":" ends with a unique column."}]]},{"t":"p","runs":[{"text":"Then a query returns the same rows today, tomorrow and after the database is upgraded, which is the minimum standard for a number anybody is going to act on."}]}],"check":{"question":[{"text":"A results page runs "},{"text":"ORDER BY created_at LIMIT 20 OFFSET 20","code":true},{"text":", and hundreds of rows share the same "},{"text":"created_at","code":true},{"text":" because they were bulk-imported. A user reports that some items appear on page 1 and again on page 2, while others never appear at all. What is happening?"}],"options":[{"text":[{"text":"Rows created between the two page requests shift everything down by one position, so page 2 repeats the last row from page 1 and hides another."}],"correct":false,"tempting":[{"text":"This is a real problem with offset pagination, but it happens with brand new rows, not with the ties described here."}]},{"text":[{"text":"Nothing, as long as "},{"text":"created_at","code":true},{"text":" has an index, because the index fixes the order of equal values."}],"correct":false,"tempting":[{"text":"Assumes an index imposes a stable secondary order; the planner may not use that index, and equal keys have no guaranteed order within it."}]},{"text":[{"text":"Rows sharing a timestamp may come back in a different order per request, so a tied row can land on both pages or on neither."}],"correct":true,"tempting":null},{"text":[{"text":"OFFSET","code":true},{"text":" skips rows before sorting, so the twenty skipped rows are not the twenty shown on page 1."}],"correct":false,"tempting":[{"text":"Misstates the order of operations: OFFSET is applied after ORDER BY, not before."}]}],"order":[1,0,2,3],"why":null},"prev":{"slug":"select-and-where","title":"SELECT and WHERE: asking for rows","minutes":7},"next":{"slug":"case-expressions","title":"CASE, COALESCE and computing a column that does not exist","minutes":7}}}