Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Sorting, Limiting, and Distinct Values
This device
Course contentsSorting, Limiting, and Distinct Values · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONConditions with WHERE
NEXT LESSONText, Dates, Patterns, and Conditional Results
Reading and filtering data · Lesson 07 120 min

Sorting, Limiting, and Distinct Values

A filtered query answers which rows qualify. This lesson asks three more questions: what order should those rows appear in, how many should be shown now, and do repeated result values have meaning? ORDER BY, LIMIT, and DISTINCT answer those questions separately.

What you will leave with

You will be able to order by one or several values, break ties with a stable key, place missing values deliberately, return a predictable page with LIMIT and OFFSET, and use DISTINCT on the exact result columns whose duplicates should collapse.

Order, page, and uniqueness solve different problems

OrderUse ORDER BY to make the result sequence intentional.
PageUse LIMIT and OFFSET to show a bounded slice of that sequence.
DeduplicateUse DISTINCT when repeated projected values are not useful.
ReviewCheck ties, NULL values, and page boundaries with concrete rows.
FILTERWHERE status = 'published'8 eligible rows

Only published courses are candidates for this catalog view.

ORDERrating DESC, course_id ASCstable tie-breaker

The key decides the order when ratings are equal.

SLICELIMIT 3 OFFSET 0first page

Return the first three rows from the ordered result.

ORDER BY makes result order a contract

A table has no inherent presentation order. Even when a query appears to return insertion order, that is not a promise. ORDER BY title ASC sorts by title in ascending order; ASC is the usual default, but writing it can make a review easier. The placement is after FROM and any WHERE, and before LIMIT.

SQL BROWSER RUNNER

Sort course titles A to Z

See how ORDER BY changes presentation without changing the ten stored rows.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT course_id, title, category
FROM courses
ORDER BY title ASC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change ASC to DESC, then put category first and title second in the ORDER BY list.

DESC reverses one sort key

ORDER BY duration_minutes DESC puts the longest courses first. A sort direction belongs to the expression immediately before it, so a later key may use a different direction. A simple sort may still leave ties. Here, CSS Layouts and HTML Semantics both take 75 minutes, while SQL Foundations and Intro to SQL both take 90. Without another key, their relative order is not guaranteed.

SQL BROWSER RUNNER

Sort longest courses first

Inspect repeated duration values and notice where the query leaves a tie unresolved.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT course_id, title, duration_minutes
FROM courses
ORDER BY duration_minutes DESC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Find the tied 75-minute and 90-minute courses. Add course_id ASC as a second sort key.

Break ties before relying on page boundaries

ORDER BY duration_minutes ASC, course_id ASC first orders by duration. Only rows with the same duration then use course_id. A unique final key gives every row a predictable position. This matters whenever you take a page: if a tie straddles the boundary, an incomplete order can make a row move between pages even when the underlying data has not changed.

SQL BROWSER RUNNER

Give equal durations a stable order

Sort by duration, then by the unique course ID.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT course_id, title, duration_minutes
FROM courses
ORDER BY duration_minutes ASC, course_id ASC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Reverse only the course_id direction. Which course comes first within each 75-minute and 90-minute tie?

Place missing values explicitly

Sorting a nullable rating has a portability trap: database engines differ in where they put NULL by default. In this SQLite runner, ascending order places NULL before numbers. The expression CASE WHEN rating IS NULL THEN 1 ELSE 0 END assigns rated rows a zero and unrated rows a one, so unrated rows go last. Rating then sorts high to low, and course ID resolves equal ratings. Some databases also offer NULLS LAST, but the support and syntax vary.

SQL BROWSER RUNNER

Keep unrated courses at the end

Use an explicit missing-value key before rating and course ID.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT course_id, title, rating
FROM courses
ORDER BY
  CASE WHEN rating IS NULL THEN 1 ELSE 0 END,
  rating DESC,
  course_id ASC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Remove the CASE expression and try rating ASC. Compare the placement of unrated courses in this SQLite runner.

LIMIT chooses a slice after ordering

LIMIT 3 asks for at most three result rows. It does not mean the three best rows unless the query defines what best means with ORDER BY. The example keeps published, rated courses, ranks them by rating, and uses course_id to break a 4.8 tie. It should return Python APIs first, then SQL Foundations, then Intro to SQL. The LIMIT syntax is supported by SQLite, PostgreSQL, and MySQL; other SQL dialects may use a different pagination clause.

SQL BROWSER RUNNER

Return the top three rated courses

Combine a clear filter, a complete order, and a three-row limit.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT course_id, title, rating
FROM courses
WHERE status = 'published' AND rating IS NOT NULL
ORDER BY rating DESC, course_id ASC
LIMIT 3;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change LIMIT to 2, then swap course_id ASC for course_id DESC to see how the tied 4.8 courses compete for the last place.

LIMIT without ORDER BY is not a stable page

The database may return any qualifying three rows when no order is requested. Adding an order with a unique final key makes the chosen slice predictable for a fixed dataset.

OFFSET skips rows in the ordered result

LIMIT 3 OFFSET 3 skips the first three qualifying rows and returns the next three. Offset zero is the first page; offset three is the second page when each page holds three rows. The example sorts published courses by duration and ID, so the second page contains course IDs 101, 110, and 108. Keep the filter and full sort rule identical between pages.

SQL BROWSER RUNNER

Read the second page

Skip the first three published courses in a stable duration order.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT course_id, title, duration_minutes
FROM courses
WHERE status = 'published'
ORDER BY duration_minutes ASC, course_id ASC
LIMIT 3 OFFSET 3;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change OFFSET to 0 and compare the pages. Then try OFFSET 6 to reach the last page.

Offsets are easy to understand, but they are not a snapshot. If a new row is inserted ahead of page two between requests, the page boundary shifts and a reader may see a duplicate or miss a row. Large offsets can also become expensive. Later, you can use a cursor based on the last seen sort keys when a changing, large dataset needs more reliable pagination.

DISTINCT removes duplicate result rows

The courses table has ten rows but only three category values: Backend, Data, and Web. SELECT DISTINCT category returns each category once. It does not delete duplicate source rows, and it does not mean that categories are unique in the table. The final ORDER BY category gives the short list a predictable display order.

SQL BROWSER RUNNER

List unique category values

Turn repeated course categories into a three-row result.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category
FROM courses
ORDER BY category ASC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Remove DISTINCT and count the repeated category rows. Restore it before moving on.

DISTINCT considers the whole selected row

SELECT DISTINCT category, status returns unique pairs, not one row per category. Data and Web each have both published and draft courses, while Backend has only published courses. The result therefore has five rows. When the query projects multiple columns, two result rows collapse only when every selected value matches. Adding course_id would make every row unique and defeat this use of DISTINCT.

SQL BROWSER RUNNER

Compare category-status pairs

See how one more projected column changes what counts as a duplicate.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category, status
FROM courses
ORDER BY category ASC, status ASC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add course_id to SELECT DISTINCT and explain why the result grows from five rows to ten.

Deduplication does not repair a wrong query

Use DISTINCT when the question truly asks for unique projected values. If an unexpected join duplicates whole entities, inspect the join and its relationship keys before hiding the extra rows with DISTINCT.

Common result-shaping mistakes

LIMIT 3 without ORDER BY

Arbitrary top three

A limit bounds count, not meaning. Order the result before choosing its first rows.

ORDER BY rating DESC

Unresolved ties and NULLs

Add a unique tie-breaker and make missing-value placement explicit when it matters.

OFFSET 3 with a changed filter

Broken page sequence

Every page must use the same filter and complete ordering to describe one sequence.

SELECT DISTINCT course_id, category

Deduplication at the wrong grain

The unique ID keeps each row distinct. Project only the values whose duplicates should collapse.

Independent lab: publish a ranked catalog page

Build a published Data-and-Web catalog ranked by rating. Put unrated courses last, break equal ratings by course_id, and show three courses per page. The starter query returns IDs 101, 110, and 102 on page one. Change OFFSET to 3 for page two; it should return IDs 103, 109, and 107. Then write a separate query that lists distinct published categories in alphabetical order.

SQL BROWSER RUNNER

Rank and page a published course feed

Combine filtering, explicit NULL placement, a unique tie-breaker, and a bounded page.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

-- Page through published Data and Web courses by rating.
-- Keep missing ratings at the end and use course_id to break ties.
SELECT course_id, title, category, rating
FROM courses
WHERE status = 'published'
  AND category IN ('Data', 'Web')
ORDER BY
  CASE WHEN rating IS NULL THEN 1 ELSE 0 END,
  rating DESC,
  course_id ASC
LIMIT 3 OFFSET 0;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify both three-row pages. Remove course_id from ORDER BY to see why the tied 4.8 and 4.2 ratings need a final key. Write SELECT DISTINCT category for the published catalog.

Lab review criteria

The first and second pages contain different course IDs, no drafts, and three rows each. The 4.8 pair and the 4.2 pair have a documented order. The unrated Accessible UI course appears after rated courses. The distinct category query returns Backend, Data, and Web exactly once.

Lesson review

ORDER BY sets presentation order, and a unique final sort key resolves ties. LIMIT returns at most a requested number of rows; OFFSET skips rows from that ordered result. DISTINCT collapses equal projected rows, so its meaning depends on the selected columns. These clauses solve different problems and are most useful when the query states the exact result contract.

  • I can sort ascending or descending and add a tie-breaker.
  • I can place NULL values deliberately when the default is unsuitable.
  • I can return a predictable page using ORDER BY, LIMIT, and OFFSET.
  • I understand why offset pages may shift when data changes.
  • I can distinguish unique category values from unique category-status pairs.
  • I know DISTINCT applies to the whole projected row.
KNOWLEDGE CHECK

Check how you shape a result

Answer all nine questions, then revisit any example whose ordering or row count surprised you.

01What does ORDER BY title ASC guarantee for a fixed result?
02Why add course_id ASC after ORDER BY duration_minutes ASC?
03Which query reliably returns the three highest-rated published courses for a fixed dataset?
04What does LIMIT 3 OFFSET 3 mean after a complete ORDER BY?
05Why place NULL ratings explicitly when sorting a catalog?
06What does SELECT DISTINCT category FROM courses return?
07What does DISTINCT compare in SELECT DISTINCT category, status?
08What can happen to OFFSET pages when a new row is inserted ahead of page two?
09Why might SELECT DISTINCT course_id, category still return every course?
PREVIOUS LESSONConditions with WHERE
NEXT LESSONText, Dates, Patterns, and Conditional Results
ON THIS PAGESorting, Limiting, and Distinct ValuesLesson mapORDER BYDescendingTie-breakersNULL placementLIMITOFFSETDISTINCT valuesDistinct rowsCommon mistakesIndependent labLesson reviewKnowledge check
Course contents