Part 4: Mastering the LIMIT Command | SQL Challenges & Capstone
SQL Challenges & Capstone · Part 4 · Lecture Notes
Mastering the LIMIT Command
How to restrict query output — previewing datasets, building Top N reports, and combining LIMIT with ORDER BY.
Part 4 of 666% Complete
LIMIT
Top N Queries
Combining with ORDER BY
8 Practice Questions
Learning Objectives
Use LIMIT to restrict query output to a specified number of rows
Combine LIMIT with ORDER BY to retrieve the top or bottom N records
Apply LIMIT alongside WHERE filters to retrieve focused ranked subsets
Understand the correct clause order: SELECT → FROM → WHERE → ORDER BY → LIMIT
Click any section header to open or close it
LIMIT restricts the number of rows returned by a query. It does not filter by value — it simply stops the result after a fixed number of rows have been returned. On large production tables containing millions of records, LIMIT is essential for keeping queries fast and output manageable.
In industry, analysts use LIMIT to preview datasets before processing them fully, retrieve top records for quick insights, reduce large outputs to a workable sample, and build Top N business reports.
The Basic Structure
LIMIT goes at the very end of a query — after FROM, after WHERE, and after ORDER BY. The number that follows LIMIT is how many rows you want returned.
Basic LIMIT Syntax
SELECT column_name
FROM table_name
LIMIT number;
LIMIT becomes most powerful when combined with ORDER BY. ORDER BY determines the ranking; LIMIT controls how many ranked rows you see. Together they answer the most common Top N business questions: top 5 customers by revenue, top 3 products by sales, most recent 10 transactions.
The Top N Pattern
Always write ORDER BY before LIMIT. The ORDER BY clause determines which rows will be at the top — LIMIT then cuts the list at the number you specify. Without ORDER BY, LIMIT returns an arbitrary set of rows with no meaningful ranking.
Top 3 Highest Transactions
SELECT *
FROM transactions
ORDER BY amount DESCLIMIT 3;
Adding a WHERE filter before ORDER BY and LIMIT produces focused Top N subsets — for example, the top 5 completed orders, or the 3 cheapest products in a specific category. The clause order is always SELECT → FROM → WHERE → ORDER BY → LIMIT.
Clause Order Matters
SQL requires clauses in a specific sequence. If you put LIMIT before ORDER BY the query will fail. The correct order for all the commands covered so far: SELECT → FROM → WHERE → ORDER BY → LIMIT.
5 Cheapest Products
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
ORDER BY unit_price ASCLIMIT 5;
First 5 Completed Orders
SELECT *
FROM orders
WHERE order_status = ‘Completed’
LIMIT 5;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Show the first 5 users
AI Prompt
Write a MySQL query to show the first 5 rows from the users table.
SQL Answer
SELECT *
FROM users
LIMIT 5;
Practice 2Show the top 3 highest transactions rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the top 3 highest transaction amounts rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
ORDER BY amount DESCLIMIT 3;
Practice 3Show the 5 cheapest products rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the 5 cheapest products rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
ORDER BY unit_price ASCLIMIT 5;
Practice 4Show the 4 most recent orders
AI Prompt
Write a MySQL query to show the 4 most recent orders.
SQL Answer
SELECT *
FROM orders
ORDER BY order_date DESCLIMIT 4;
Practice 5Show the first 2 support tickets
AI Prompt
Write a MySQL query to show the first 2 support tickets.
SQL Answer
SELECT *
FROM support_tickets
LIMIT 2;
Practice 6Show the top 5 subscriptions by monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the top 5 subscriptions by monthly_fee rounded to 2 decimal places.
SQL Answer
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
ORDER BY monthly_fee DESCLIMIT 5;
Practice 7Show the 3 most recent transactions rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the 3 most recent transactions rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount,
transaction_date
FROM transactions
ORDER BY transaction_date DESCLIMIT 3;
Practice 8Show the first 5 completed orders
AI Prompt
Write a MySQL query to show the first 5 completed orders.
SQL Answer
SELECT *
FROM orders
WHERE order_status = ‘Completed’
LIMIT 5;
Knowledge Check
Five questions — select the best answer for each.
1. What does LIMIT do in a SQL query?
AIt filters rows based on a value condition
BIt restricts the number of rows returned
CIt sorts rows in descending order
DIt removes duplicate rows
Correct. LIMIT stops the query after returning the specified number of rows — it does not filter by value, only by row count.
Not quite. LIMIT restricts the row count returned — it doesn’t filter by value, it just caps the number of rows.
2. To retrieve the top 5 most expensive products, which query structure is correct?
ASELECT * FROM products LIMIT 5 ORDER BY unit_price DESC;
BSELECT * FROM products ORDER BY unit_price DESC LIMIT 5;
CSELECT * FROM products HAVING unit_price DESC LIMIT 5;
DSELECT TOP 5 * FROM products ORDER BY unit_price DESC;
Correct. ORDER BY comes before LIMIT. The ORDER BY clause sorts the rows; LIMIT then cuts the list at 5.
Not quite. ORDER BY must come before LIMIT: SELECT * FROM products ORDER BY unit_price DESC LIMIT 5;
3. What is the correct clause order when using WHERE, ORDER BY, and LIMIT together?
ASELECT → FROM → LIMIT → WHERE → ORDER BY
BSELECT → FROM → ORDER BY → WHERE → LIMIT
CSELECT → FROM → WHERE → ORDER BY → LIMIT
DSELECT → LIMIT → FROM → WHERE → ORDER BY
Correct. SQL requires a strict clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT. Deviating from this order produces an error.
Not quite. The correct order is: SELECT → FROM → WHERE → ORDER BY → LIMIT.
4. An analyst runs SELECT * FROM users LIMIT 3. What does this return?
AThe 3 users with the highest user_id
BThe 3 users with the most recent signup_date
CThe first 3 rows as stored in the table
D3 randomly selected users
Correct. Without ORDER BY, LIMIT simply returns the first N rows as the database has stored them — no ranking or sorting is applied.
Not quite. Without ORDER BY, LIMIT returns the first N rows as stored — not ranked, not sorted, not random.
5. Why is LIMIT important when working with large production databases?
AIt prevents other users from accessing the table
BIt permanently deletes rows beyond the limit
CIt reduces output size, making queries faster and results more manageable
DIt creates an index on the table to speed up future queries
Correct. On tables with millions of rows, returning everything is slow and the output is unmanageable. LIMIT keeps queries efficient and results focused.
Not quite. LIMIT reduces the number of rows returned, making queries faster and output manageable on large tables.
Part 4 Summary
The key ideas from this lecture — keep these in mind as you move forward.
LIMIT helps analysts retrieve focused subsets of data, especially when working with large business datasets and Top N reporting. Always combine LIMIT with ORDER BY when ranking matters. The correct clause order is: SELECT → FROM → WHERE → ORDER BY → LIMIT.
