ORDER BY and LIMIT

Lesson Description

Sort rows and trim the result.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

ORDER BY sorts the result set. LIMIT caps the number of rows returned.

Sorting with ORDER BY

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

ASC (ascending, A→Z, 0→9) is the default. DESC reverses it.

Sorting by multiple columns

Rows are sorted by the first column, then by the second for ties:

SELECT first_name, last_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

LIMIT — return only N rows

-- Top 5 earners
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

OFFSET — skip N rows

Use with LIMIT for pagination:

-- Rows 11–20 ordered by last name
SELECT first_name, last_name
FROM employees
ORDER BY last_name
LIMIT 10 OFFSET 10;

Stable tie-breaking

When two rows have identical sort values, the order between them is undefined. Add a unique column (like id) as a final sort key to guarantee a deterministic result:

ORDER BY salary DESC, id ASC

Tips

ORDER BY is almost always the last clause in a query.
LIMIT without ORDER BY returns an arbitrary subset — rarely what you want.
Column aliases defined in SELECT can be used in ORDER BY.

Learning context

This lesson is part of SQL Fundamentals. Focus on the core idea in ORDER BY and LIMIT, then validate with deliberate practice.

What to master

Understand clause order and intent
Write deterministic result queries
Use clear aliases and readable structure

Common mistakes

Skipping ORDER BY in final outputs
Using joins before validating base rows
Not checking row counts after changes

High-level strategy

Start simple, then layer complexity
Run often and compare expected shape
Refactor with CTEs when logic grows

Task ladder

Baseline query: get a correct first output.
Add one complication: edge case, join, filter, or ranking rule.
Re-run and refine until output is stable and explainable.

Transparent data checks

Check row count before and after each major clause.
Validate one manual sample row for correctness.
Confirm final ordering and column naming for trust.

Retention loop

Run the solution once.
Rewrite from memory with one variation.
Explain the logic in plain English in one sentence.

Logical reasoning for commands

WHERE

Why: Limits rows to only the business-relevant subset.

Memory cue: Filter early to reduce noise.

ORDER BY

Why: Makes output deterministic and reviewable.

Memory cue: No ORDER BY means no guaranteed row order.

AS alias

Why: Makes output columns readable for teams and reports.

Memory cue: If the name is clear, the query is easier to trust.

Concept check

Quick check: ORDER BY and LIMIT

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Top 5 earners

Enterprise Workforce Analyticseasy

Return the first_name, last_name, and salary of the 5 highest-paid employees, ordered by salary descending.

Run a query to see results here.

Exercise 2

Cheapest 3 products

Commerce Operations Intelligenceeasy

From the products table in the store sandbox, return the name and price of the 3 cheapest products, ordered by price ascending.

Run a query to see results here.

Exercise 3

Employees in alphabetical order

Enterprise Workforce Analyticseasy

Return first_name and last_name of all employees ordered by last_name ascending, then first_name ascending.

Run a query to see results here.

Exercise 4

Top 3 most expensive in-stock products

Commerce Operations Intelligenceeasy

From the store sandbox, return name and price of the 3 most expensive products that are in stock (stock > 0), ordered by price descending.

Run a query to see results here.

Lesson 3 of 5

0/4 solved (0%)

← Previous

Finish this lesson to unlock next.