Sorting & limiting

ORDER BY determinism, ties at the LIMIT boundary, NULL sort position, ordinal vs expression.

• ★LIMIT without ORDER BY

  LIMIT without ORDER BY returns an arbitrary set — 'it worked yesterday' until the plan changes.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE parcels(id INT, grams INT); INSERT INTO parcels VALUES (1,900),(2,300),(3,1500),(4,600),(5,1200);

  The trap

  SELECT id FROM parcels LIMIT 3;

  0 rows

  The fix

  SELECT id FROM parcels ORDER BY grams DESC LIMIT 3;

  3

  5

  1

  The 'three heaviest parcels' shortlist is whatever the scan returned — reload the table and it silently changes.

• Ties at the LIMIT boundary

  A top-10 that should be top-10-with-ties cuts peers arbitrarily — WITH TIES / RANK is the bar.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE kites(name VARCHAR, height INT); INSERT INTO kites VALUES ('red',90),('blue',80),('green',80),('yellow',60);

  The trap

  SELECT name, height FROM kites ORDER BY height DESC LIMIT 2;

  red	90
  blue	80

  The fix

  SELECT name, height FROM (SELECT name, height, RANK() OVER (ORDER BY height DESC) AS rnk FROM kites) WHERE rnk <= 2 ORDER BY height DESC, name;

  red	90
  blue	80
  green	80

  The top-2 board dropped one of the two kites tied at 80 — LIMIT 2 cuts through the tie arbitrarily, where RANK() <= 2 keeps every medalist; DuckDB rejects WITH TIES (Postgres 13+ has it).

• NULL sort position differs by engine

  Whether NULLs sort first or last is engine-dependent — NULLS FIRST/LAST is the explicit bar.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE rounds(player VARCHAR, strokes INT); INSERT INTO rounds VALUES ('Ada',54),('Bo',NULL),('Cy',48),('Di',61);

  The trap

  SELECT player FROM rounds ORDER BY strokes ASC;

  Cy

  Ada

  Di

  Bo

  The fix

  SELECT player FROM rounds ORDER BY strokes ASC NULLS LAST;

  Cy

  Ada

  Di

  Bo

  The clubhouse board looks sorted, but where the rain-out NULL lands is engine-dependent — the same ORDER BY drops it first on one engine and last on another.

• ORDER BY ordinal vs expression

  ORDER BY 2 is positional and brittle; ordering by a column absent from SELECT DISTINCT is undefined.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE setlist(song VARCHAR, bpm INT); INSERT INTO setlist VALUES ('adagio',200),('brindisi',120);

  The trap

  SELECT bpm, song FROM setlist ORDER BY 2 DESC;

  120	brindisi
  200	adagio

  The fix

  SELECT bpm, song FROM setlist ORDER BY bpm DESC;

  200	adagio
  120	brindisi

  After a column was reordered, ORDER BY 2 sorted by the song name, not the bpm — an ordinal binds to the SELECT list's position, so editing the columns silently changes what you sort by; naming the column is stable.

• OFFSET pagination drift

  OFFSET pagination re-runs the query per page — rows inserted or deleted between requests shift the offsets, so items get skipped or shown twice.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE stalls(id INT, name VARCHAR); INSERT INTO stalls VALUES (1,'amber'),(3,'copal'),(4,'drift');

  The trap

  SELECT name FROM stalls ORDER BY id LIMIT 2 OFFSET 2;

  drift

  The fix

  SELECT name FROM stalls WHERE id > 2 ORDER BY id LIMIT 2;

  copal

  drift

  After a stall (id 2) closed between page loads, page 2's OFFSET 2 skipped 'copal' entirely — OFFSET counts rows in the table as it is now, so a delete above the boundary pulls an unseen row into skipped territory; keyset paging on the last id shows it.

• Collation and case in string order

  Default string ordering is collation-defined — binary collations sort every capital before every lowercase ('Zeb' < 'ana'); case handling is a setting, not a law.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE names(n VARCHAR); INSERT INTO names VALUES ('ana'),('Zeb'),('bo');

  The trap

  SELECT n FROM names ORDER BY n;

  Zeb

  ana

  bo

  The fix

  SELECT n FROM names ORDER BY lower(n);

  ana

  bo

  Zeb

  The surname index printed every capital before every lowercase — the default binary collation compares code points, where 'Z' (90) < 'a' (97), so 'Zeb' sorts before 'ana'; ordering by lower(n) reads alphabetically.