02.2 Sorting and limiting results

01 | A TABLE HAS NO PROMISED DISPLAY ORDER

A database may appear to return rows in insertion order during a small test. That does not make it a guarantee. Use ORDER BY when order matters.

This is especially important for “top three”, pages of results and repeated reports. LIMIT alone does not tell the database which records should be first.

Use this section’s expanded Clubs table. It is a separate retrieval dataset , not a migration of the Section 01 database. All examples use SQLite. The queries below read data without changing stored records.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    room TEXT,
    fee_cents INTEGER NOT NULL,
    session_minutes INTEGER NOT NULL
);

INSERT INTO Clubs
    (club_id, club_name, category, capacity, room, fee_cents, session_minutes)
VALUES
    (1, 'Robotics', 'Technology', 12, 'AIR Lab', 1500, 90),
    (2, 'Game Design', 'Technology', 16, 'Room 12', 0, 60),
    (3, 'Art', 'Creative', 12, 'Studio', 800, 60),
    (4, 'Film', 'Creative', 20, NULL, 1000, 90),
    (5, 'Chess', 'Strategy', 16, 'Room 12', 0, 45),
    (6, 'Coding', 'Technology', 20, 'AIR Lab', 0, 60),
    (7, 'Drama', 'Creative', 24, NULL, 1200, 90),
    (8, 'Robotics Junior', 'Technology', 10, '', 500, 45);
"""

query = """
SELECT club_id, club_name, category, capacity, room, fee_cents, session_minutes
FROM Clubs
ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | SHARED REFERENCE DATA

Money is stored as integer cents to avoid using binary floating point as the stored monetary amount. fee_cents = 0 means free, not unknown. Film and Drama have NULL rooms; Robotics Junior has an empty text room, deliberately included so you can compare the two situations.

03 | ORDER BY A NUMBER

SELECT club_name, capacity
FROM Clubs
ORDER BY capacity ASC, club_id ASC;

ASC means ascending: smaller capacity first. It is the default if the direction is omitted. The second column is a tie-breaker, explained below.

ORDER BY arranges result rows. It does not physically rearrange the stored table or change any capacity.

04 | DESC REVERSES THE DIRECTION

SELECT club_name, capacity
FROM Clubs
ORDER BY capacity DESC, club_id ASC;

DESC puts larger values first. Each column has its own direction: here capacity decreases, but IDs increase within a capacity tie.

Do not assume one final DESC applies to every listed column. Be explicit while learning.

05 | TIES NEED ANOTHER RULE

Film and Coding both have capacity 20. Art and Robotics both have capacity 12. Sorting only by capacity leaves the order within each tie unspecified.

A unique final tie-breaker such as club_id produces a deterministic order for this fixed data. Sorting by name can also be useful, but names are not always unique.

SELECT club_id, club_name, capacity
FROM Clubs
WHERE capacity = 20
ORDER BY capacity DESC, club_id ASC;

06 | SORT TEXT WITH A DEFINED EXPECTATION

SELECT club_name
FROM Clubs
ORDER BY club_name ASC, club_id ASC;

Text order depends on collation, the rules used for comparison. The ASCII title-case names here sort as shown under SQLite’s default comparison.

Real text with mixed case and accented characters may need a deliberate collation strategy. Do not assume every database automatically uses the alphabetical rules of every language.

07 | LIMIT THE ORDERED RESULT

SELECT club_name, capacity
FROM Clubs
ORDER BY capacity DESC, club_id ASC
LIMIT 3;

LIMIT 3 returns at most three rows. It may return fewer if the query has fewer matches. The result is Drama, Film and Coding with this ordering.

LIMIT does not include every row tied at the boundary automatically. Use the specified tie-breaker to decide the slice. LIMIT 0 returns no rows.

08 | FILTER, SORT, THEN LIMIT

SELECT club_name, capacity
FROM Clubs
WHERE category = 'Technology'
ORDER BY capacity DESC, club_id ASC
LIMIT 2;

A useful logical reading is: find matching rows, sort them and take the requested slice. This is a mental model, not a requirement that the database physically executes every stage in that order.

The written clause order is SELECT, FROM, WHERE, ORDER BY, LIMIT. Putting WHERE after LIMIT is a syntax error.

09 | OFFSET SKIPS ROWS

SELECT club_id, club_name
FROM Clubs
ORDER BY club_id ASC
LIMIT 3 OFFSET 3;

OFFSET 3 skips the first three ordered rows, then LIMIT 3 returns the next three. An offset of zero starts at the beginning.

For pages of three rows, offsets are 0, 3 and 6. Rows 7 and 8 form the last page here. Always use a stable ordering; changes to the data between requests can still shift page contents.

10 | NULL AND ORDERING

SELECT club_id, club_name, room
FROM Clubs
ORDER BY room ASC, club_id ASC;

In ordinary SQLite ascending order, NULL sorts before non-NULL values. Empty text is a value of its own, not NULL. Film and Drama therefore appear before the empty-string room in this example.

Other products can use different default NULL ordering. If placement matters, choose explicit supported ordering rules rather than rely on portability of a default.

11 | PRACTISE THE SLICE

SELECT club_name, capacity
FROM Clubs
WHERE fee_cents = 0
ORDER BY capacity DESC, club_id ASC
LIMIT 2;

SELECT club_id, club_name, capacity
FROM Clubs
ORDER BY capacity DESC, club_id ASC
LIMIT 2 OFFSET 2;

KEEP LEARNING

Previous page 02.3 Filtering with LIKE, IN, BETWEEN and NULL

Reference: SQLite SELECT · SQLite expressions and filters