Sorting and Limiting

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.

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','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','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.

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.

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.

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.

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.

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.

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.

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.

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.

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.