Part 1: Mastering the LIMIT Command | SQL Challenges & Capstone
SQL Filtering & Search Commands Practice · Part 1 · Lecture Notes
Mastering the LIMIT Command
How to restrict query output — previewing datasets, building Top N reports, and combining LIMIT with ORDER BY for ranked results.
Part 1 of 616% Complete
LIMIT
Top N Analysis
ORDER BY
Previewing Datasets
8 Practice Questions
Learning Objectives
Use LIMIT to restrict query output to a specified number of rows
Combine LIMIT with ORDER BY to retrieve top or bottom N ranked records
Apply LIMIT to quickly preview unfamiliar tables before writing larger queries
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. On large production tables with millions of records, returning everything is slow and unmanageable. LIMIT keeps queries efficient by capping the output at exactly the number of rows you need.
In industry, analysts use LIMIT to preview datasets before processing them fully, inspect sample records, retrieve top-performing results, reduce large outputs to a workable size, and focus on high-priority business records.
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 exactly how many rows will be returned.
Basic LIMIT Syntax
SELECT column_name
FROM table_name
LIMIT number;
Without LIMIT, a query returns every matching row. LIMIT stops the result after the specified count — regardless of how many rows exist in the table. On a table with 8 rows, LIMIT 3 returns the first 3. On a table with 8 million rows, it still returns only 3.
Why LIMIT Matters on Large Datasets
In production environments, tables can have hundreds of thousands or millions of rows. Running SELECT * without LIMIT on a table that size can crash a session or produce output no analyst can read. LIMIT is the discipline of retrieving only what you need.
First 5 Users
SELECT *
FROM users
LIMIT 5;
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 analysts face every day.
The Top N Pattern
Always write ORDER BY before LIMIT. ORDER BY sorts all the rows; LIMIT then cuts the list at your specified number. Without ORDER BY, LIMIT returns an arbitrary set with no meaningful ranking.
Top 3 Highest Transaction Amounts
SELECT *
FROM transactions
ORDER BY amount DESCLIMIT 3;
2 Highest Subscription Fees
SELECT *
FROM subscriptions
ORDER BY monthly_fee DESCLIMIT 2;
When analysts encounter a new table for the first time, SELECT * with LIMIT is the standard first query. It returns a small sample of rows — enough to understand the column names, data types, and value ranges — without overwhelming the output.
Standard First Query on a New Table
SELECT * FROM table_name LIMIT 3; is the analyst equivalent of opening a spreadsheet and reading the first few rows before deciding what to do with the data. It confirms the columns are what you expect and the data is populated.
Preview the Products Table
SELECT *
FROM products
LIMIT 3;
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 1Retrieve the first 3 records from the users table
AI Prompt
Write a MySQL query to show the first 3 records from the users table.
SQL Answer
SELECT *
FROM users
LIMIT 3;
Practice 2Retrieve the 5 highest transaction amounts rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the 5 highest transaction amounts rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
ORDER BY amount DESCLIMIT 5;
Practice 3Retrieve the first 4 products from the products table
AI Prompt
Write a MySQL query to show the first 4 products from the products table.
SQL Answer
SELECT *
FROM products
LIMIT 4;
Practice 4Retrieve the 2 most recent orders
AI Prompt
Write a MySQL query to show the 2 most recent orders.
SQL Answer
SELECT *
FROM orders
ORDER BY order_date DESCLIMIT 2;
Practice 5Retrieve the first 3 support tickets
AI Prompt
Write a MySQL query to show the first 3 support tickets.
SQL Answer
SELECT *
FROM support_tickets
LIMIT 3;
Practice 6Retrieve the 3 highest subscription monthly fees rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the 3 highest subscription monthly fees rounded to 2 decimal places.
SQL Answer
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
ORDER BY monthly_fee DESCLIMIT 3;
Practice 7Retrieve the first 5 campaign events
AI Prompt
Write a MySQL query to show the first 5 campaign events.
SQL Answer
SELECT *
FROM campaign_events
LIMIT 5;
Practice 8Retrieve the 4 lowest product prices rounded to 2 decimal places
AI Prompt
Write a MySQL query to show the 4 lowest product prices rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
ORDER BY unit_price ASCLIMIT 4;
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 sorts rows from highest to lowest
CIt restricts the number of rows returned
DIt removes duplicate rows
Correct. LIMIT caps the row count returned by a query — it doesn’t filter by value or sort. It simply stops the output after the specified number of rows.
Not quite. LIMIT restricts the row count — it doesn’t filter by value, sort, or remove duplicates.
2. Which query returns the 3 highest transaction amounts?
ASELECT * FROM transactions LIMIT 3;
BSELECT * FROM transactions WHERE amount > 3;
CSELECT * FROM transactions ORDER BY amount DESC LIMIT 3;
DSELECT * FROM transactions ORDER BY amount ASC LIMIT 3;
Correct. ORDER BY amount DESC ranks from highest to lowest; LIMIT 3 then cuts the list at 3 rows — producing the top 3 amounts.
Not quite. You need ORDER BY amount DESC to rank highest-first, then LIMIT 3 to cap at 3 rows.
3. Why do analysts use LIMIT when previewing a new table?
ATo permanently delete extra rows from the table
BTo see a small sample of rows before processing the full dataset
CTo sort the table by primary key
DTo count the total number of rows in the table
Correct. SELECT * FROM table LIMIT 3 is the standard preview query — it returns a small, readable sample without overwhelming the output or slowing the session.
Not quite. LIMIT is used to preview a manageable sample of rows from a new table before running larger queries.
4. What is the correct clause order when using ORDER BY and LIMIT together?
ALIMIT → ORDER BY → FROM → SELECT
BSELECT → FROM → LIMIT → ORDER BY
CSELECT → FROM → ORDER BY → LIMIT
DORDER BY → SELECT → FROM → LIMIT
Correct. SQL requires a strict clause order: SELECT, FROM, WHERE (if present), ORDER BY, LIMIT. LIMIT always comes last.
Not quite. The correct order is SELECT → FROM → WHERE → ORDER BY → LIMIT. LIMIT always comes after ORDER BY.
5. An analyst writes SELECT * FROM products LIMIT 4 ORDER BY unit_price ASC. What happens?
AReturns the 4 cheapest products
BReturns a syntax error — LIMIT must come after ORDER BY
CReturns the first 4 rows then sorts them
DReturns all products sorted by price
Correct. SQL clause order is strict. LIMIT must follow ORDER BY — putting LIMIT before ORDER BY produces a syntax error.
Not quite. LIMIT must come after ORDER BY in SQL. Writing LIMIT before ORDER BY produces a syntax error.
Part 1 Summary
The key ideas from this lecture — keep these in mind as you move forward.
LIMIT restricts query output so analysts can preview datasets, retrieve top records, and focus on the most important business information. Always place LIMIT after ORDER BY. The correct clause order is: SELECT → FROM → WHERE → ORDER BY → LIMIT.
