--- title: 'Sorting and Limiting' source: 'https://academia.sh/en/courses/sql-fundamentals/sorting-and-limiting' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Sorting and Limiting Sorting the result set, multi-key sorting, collation's effect on order, where null values land, and the standard versus common syntax for limiting the result to the first rows. The previous three lessons determined which columns and which rows would come back. None of them said in which order the rows would come back. The order seen in the outputs came from how the engine read the table; the same query can come back in a different order when a different plan is chosen. This is not surprising, since a relation in the relational model is an unordered set: order is a property of the query, not of the data. If order is wanted, it has to be written explicitly. This lesson builds the sorting clause and, alongside it, the limiting syntax that only makes sense together with it. The block below sets up the schema and sample data; every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` ## Sorting by a Single Key The `ORDER BY` clause takes the sort key. If no direction is stated, ascending order is assumed; `ASC` and `DESC` state the direction explicitly. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year; SQL ``` ``` title publication_year ----------------- ---------------- Motherland Hotel Yaban 1932 The Disconnected 1972 Tehlikeli Oyunlar 1973 The Book of Sand 1975 Silent House 1983 Blindness 1995 ``` The book with an unknown publication year came first. Where a null value lands in a sort is theoretically undefined — it cannot be said whether an unknown number is greater or less than 1932 — so engines make an assumption, and that assumption depends on the engine. Some place null values first in ascending order, others place them last. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year DESC; SQL ``` ``` title publication_year ----------------- ---------------- Blindness 1995 Silent House 1983 The Book of Sand 1975 Tehlikeli Oyunlar 1973 The Disconnected 1972 Yaban 1932 Motherland Hotel ``` When the direction reversed, the null value switched sides too. There is a standard marker for pinning its place in the query. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year NULLS LAST; SQL ``` ``` title publication_year ----------------- ---------------- Yaban 1932 The Disconnected 1972 Tehlikeli Oyunlar 1973 The Book of Sand 1975 Silent House 1983 Blindness 1995 Motherland Hotel ``` The `NULLS FIRST` and `NULLS LAST` markers can move null values to the end even in ascending order. Support for them depends on the engine; on engines that do not support them, the same result is obtained by making an expression that tests for null the first sort key. ## Collation and Text Order The result of sorting text depends on collation. If the default collation compares characters by their codes rather than alphabetically, the result diverges from Turkish alphabetical order. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book ORDER BY author; SQL ``` ``` author ----------------- Jorge Luis Borges José Saramago Orhan Pamuk Oğuz Atay Oğuz Atay Yakup Kadri Yusuf Atılgan ``` `Orhan Pamuk` came before `Oğuz Atay`. In the Turkish alphabet, `ğ` comes right after `g`, well before `r`; the comparison applied here, though, compares the binary representation of the characters, and `ğ`'s code point is far higher than `r`'s, so `Orhan` came out first. This is not a bug, but the result of a configuration. If language-aware sorting is wanted, a language-specific collation is defined on the column or the query; which collations exist and how they are named depends on the engine. In lists shown to users, this is a defect that is hard to notice once it has been overlooked. ## Multi-Key Sorting Multiple keys are separated by commas and applied in order: rows that are equal on the first key get sorted by the second key. Each key's direction is written separately. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author, publication_year FROM book ORDER BY author, publication_year DESC; SQL ``` ``` title author publication_year ----------------- ----------------- ---------------- The Book of Sand Jorge Luis Borges 1975 Blindness José Saramago 1995 Silent House Orhan Pamuk 1983 Tehlikeli Oyunlar Oğuz Atay 1973 The Disconnected Oğuz Atay 1972 Yaban Yakup Kadri 1932 Motherland Hotel Yusuf Atılgan ``` The same author's two books were sorted from newest to oldest. `DESC` applies only to its own key; the author column stayed ascending. A sort key can also be an expression, and the result column's ordinal position can be written too. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY 2 DESC; SQL ``` ``` title publication_year ----------------- ---------------- Blindness 1995 Silent House 1983 The Book of Sand 1975 Tehlikeli Oyunlar 1973 The Disconnected 1972 Yaban 1932 Motherland Hotel ``` Writing it by ordinal position is short but fragile: when a column gets added to the column list, the sort silently shifts to a different column. Writing the column name or alias is always safer. ## Deterministic Sorting `ORDER BY` establishes an order only over the keys it is written with. The order among rows that are equal on those keys is undefined, and it can change when the engine changes its plan. In the query above, the publication year was the tiebreaker when the author was equal; had both been equal, the result could have differed from run to run. When the result needs to be the same on every run — reports written to a log, paginated lists, compared outputs — a unique column gets added to the end of the sort keys. The primary key is directly suited to this job. Writing `ORDER BY author, book_id` defines a single order even among rows whose author is the same. ## Limiting When only the first few rows of a sorted result are wanted, limiting is written. The standard syntax and the common shortcut differ, and this is the second dialect distinction encountered in the course. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title FROM book ORDER BY title FETCH FIRST 3 ROWS ONLY; SQL ``` ``` Parse error near line 3: near "FETCH": syntax error SELECT title FROM book ORDER BY title FETCH FIRST 3 ROWS ONLY; error here ---^ ``` The standard syntax is `OFFSET … ROWS FETCH FIRST … ROWS ONLY`, and the engine used here does not recognize it. The syntax the engine accepts is the shortcut common across a wide range of engines. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year DESC LIMIT 3; SQL ``` ``` title publication_year ---------------- ---------------- Blindness 1995 Silent House 1983 The Book of Sand 1975 ``` The number of rows to skip can be stated too. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year DESC LIMIT 3 OFFSET 3; SQL ``` ``` title publication_year ----------------- ---------------- Tehlikeli Oyunlar 1973 The Disconnected 1972 Yaban 1932 ``` Together, the two queries produced two pages of three rows each. The pagination syntax has two traps. First, writing a limit without writing a sort is meaningless: which rows are the "first three" stays undefined, and two consecutive pages can show the same row twice. Second, pagination by skip count drifts when data changes between pages — a deleted row makes the next page skip its first row. The second of these two traps gets solved with keyset pagination in the Advanced SQL course. The performance cost of the skip count is silent too: the engine still has to produce the skipped rows before discarding them, so large offset values produce lists that get slower as the page number grows. ## Summary - Sorting is a property of the query, not of the data; without `ORDER BY`, row order is undefined. - Where null values land in a sort depends on the engine; `NULLS FIRST` and `NULLS LAST` pin their place, but support for them also depends on the engine. - Text order depends on collation; the default collation may not give Turkish alphabetical order. - In multi-key sorting, each key's direction is written separately, and the keys apply in order. - The order among rows with equal keys is undefined; a unique column is added to the sort for a deterministic result. - The standard syntax for limiting and the common shortcut differ; limiting without sorting is meaningless, and offset-based pagination drifts when data changes. ## Next Step Over the last three lessons, a null value showed up in three separate places: it blanked out a computed expression, left a condition's negation incomplete, and took an engine-dependent place in sorting. These are consequences of a single rule. The next lesson builds that rule — the three-valued logic of an unknown value — introduces the functions that work with null values, and measures the trap that shows up in the negation of a membership condition.