DISTINCT, ORDER BY, LIMIT — Shaping the Result Set

Data we'll use — the score table loaded from CSV

This article uses a `score` table loaded automatically from a CSV file to step through the four constructs that shape SELECT output — DISTINCT (deduplication), ORDER BY (sorting), LIMIT (row count), and OFFSET (skip from the top).

The score table has 5 columns — id / name / subject / score / recorded_on — with 15 rows total: 5 students (Alice / Bob / Carol / Dave / Eve) each took 3 subjects (Math / English / Science).

Before the exercises, confirm the score table's column definitions and a sample of the data.

① Run PRAGMA table_info(score); to check column names, types, and primary keys.

② Run SELECT * FROM score LIMIT 5; to preview the first 5 rows.

DISTINCT — return only unique rows

A SELECT result can contain the same row multiple times. For example, pulling just the name column from score returns 15 rows — Alice / Bob / Carol / Dave / Eve, three subjects each.

Writing `SELECT DISTINCT col, ...` collapses duplicate rows into one in the result set. With multiple columns, rows are considered duplicates when the combination of columns matches.

-- List unique student names
SELECT DISTINCT name FROM score;

-- List unique subjects
SELECT DISTINCT subject FROM score;

-- Dedup on the (name, subject) combination (= all 15 rows are unique)
SELECT DISTINCT name, subject FROM score;

Run two queries side by side in one console to see how the result changes with and without DISTINCT. (Once you run it correctly, the explanation will appear.)

① From score, pull the name column without `DISTINCT`.

② In the same console, pull the name column with `DISTINCT`.

③ Confirm the first returns 15 rows (each name 3 times), and the second returns 5 rows (duplicates collapsed).

ORDER BY — sort the result

SQL does not guarantee row order by default — SELECT * FROM score; lets the DB implementation decide what order rows come back in. When you want a specific order, add `ORDER BY col [ASC|DESC]`.

- ASC (ascending / small → large) is the default and can be omitted

- DESC (descending / large → small) must be written explicitly

- A comma-separated list of multiple columns gives you a multi-level sort: rows tied on the first column get ordered by the second

-- 1) Single-column ascending (ASC can be omitted)
SELECT name, score FROM score ORDER BY score;

-- 2) Single-column descending
SELECT name, score FROM score ORDER BY score DESC;

-- 3) Multi-column sort (by subject, then highest score first within a subject)
SELECT name, subject, score FROM score ORDER BY subject ASC, score DESC;

Fine print on ORDER BY

- ORDER BY goes after `WHERE` and before `LIMIT`. Memorize the order SELECT cols FROM table WHERE condition ORDER BY col DESC LIMIT N; and you won't get tripped up.

- The right-hand side of ORDER BY can take a column number (ORDER BY 2 DESC sorts by the 2nd selected column), but it hurts readability — the convention is to use column names.

- In this course's console, string sorting is uppercase → lowercase (ASCII order). Some DBs treat 'A' and 'a' as equivalent.

Imagine a score listing screen where you want students sorted by name, and within each student, the lowest-scoring subject shown first.

① From score, pull the name, subject, and score columns.

② Sort by `name` ascending, and within the same name by `score` ascending.

③ Confirm the result is 15 rows, starting with Alice Science 78 and ending with Eve English 93.

Same name order as Practice 2, but this time you want the highest-scoring subjects shown first within each student.

① From score, pull the name, subject, and score columns.

② Sort by `name` ascending, and within the same name by `score` descending.

③ Confirm the result is 15 rows, starting with Alice Math 92 and ending with Eve Science 86.

LIMIT and OFFSET — narrow the count and paginate

When result sets get large — 100 rows, 10,000 rows — dumping everything to the screen is heavy and the user can't take it in. Adding `LIMIT N` returns only the first N rows. Combine it with `OFFSET M` to skip the first M rows and then take N, which gives you pagination for things like "items 6–10" or "items 11–20."

-- 1) Take the top 5 (ranking TOP 5)
SELECT name, subject, score FROM score
ORDER BY score DESC
LIMIT 5;

-- 2) Take items 6 through 10 (page 2)
SELECT name, subject, score FROM score
ORDER BY score DESC
LIMIT 5 OFFSET 5;

-- 3) Pagination formula: page N, K rows per page
--    LIMIT K OFFSET (N - 1) * K
--    e.g. page 3, 5 per page → LIMIT 5 OFFSET 10

LIMIT without ORDER BY doesn't guarantee order

Writing just `LIMIT` and skipping `ORDER BY` like SELECT * FROM score LIMIT 5; leaves the choice of which 5 rows come back up to the DB implementation. Even on a DB that tends to return rows in insert order (like SQLite), the SQL standard says order is undefined. On MySQL / PostgreSQL, indexes and execution plans can swap which rows you get.

For anything where order matters — rankings, pagination, "first N items" — always write `ORDER BY` before `LIMIT`.

Imagine a dashboard that needs a "top 5 across all subjects" widget.

① From score, pull the name, subject, and score columns.

② Sort by `score` descending, and take only the first 5 rows.

③ Confirm the result is 5 rows, starting with Carol Math 95 and ending with Bob English 88.

Imagine a "Show more" button on a dashboard that loads the next 5 items after the top 5.

① Same sort order as Practice 4 (score descending), pulling the name, subject, and score columns.

② Skip the first 5 rows and take the next 5 (ranks 6–10).

③ Confirm the result is 5 rows, starting with Eve Math 87 and ending with Alice Science 78.