TOP vs LIMIT across databases in SQL - Hands-On Comparison

Using TOP and LIMIT to Control Query Results

📖 Scenario: You work in a small bookstore's database team. The manager wants to see only a few top-selling books from the sales records to decide which books to promote.

🎯 Goal: Build SQL queries that show how to get the first 3 rows from a sales table using both TOP and LIMIT clauses, demonstrating their use in different databases.

📋 What You'll Learn

Create a table called sales with columns book_title (text) and copies_sold (integer).

Insert 5 rows with exact book titles and copies sold values.

Write a query using TOP to select the first 3 rows ordered by copies_sold descending.

Write a query using LIMIT to select the first 3 rows ordered by copies_sold descending.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses often want to see only the top few records from large datasets to make quick decisions.

💼 Career

Knowing how to limit query results is essential for database developers and analysts to optimize performance and deliver relevant data.

1

Create the sales table and insert data

Create a table called sales with columns book_title (text) and copies_sold (integer). Then insert these exact rows: ('The Alchemist', 150), ('1984', 200), ('The Hobbit', 180), ('Pride and Prejudice', 120), ('To Kill a Mockingbird', 170).

SQL

-- Create table sales and insert 5 rows
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO with multiple rows to add data.

2

Set up ordering for queries

Create a variable or comment to remind yourself to order the sales by copies_sold in descending order before selecting top rows.

SQL

CREATE TABLE sales (book_title TEXT, copies_sold INTEGER);
INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 150),
('1984', 200),
('The Hobbit', 180),
('Pride and Prejudice', 120),
('To Kill a Mockingbird', 170);
-- Remember to order by copies_sold DESC
-- Your code here

Hint

Ordering helps to get the top selling books first.

3

Write a query using TOP to get top 3 books

Write a SQL query using SELECT TOP 3 to get the first 3 rows from sales ordered by copies_sold descending.

SQL

CREATE TABLE sales (book_title TEXT, copies_sold INTEGER);
INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 150),
('1984', 200),
('The Hobbit', 180),
('Pride and Prejudice', 120),
('To Kill a Mockingbird', 170);
-- We will order by copies_sold DESC in queries
SELECT TOP 3 book_title, copies_sold FROM sales ORDER BY copies_sold DESC;
-- Your code here

Hint

Use TOP 3 right after SELECT to get the first 3 rows.

4

Write a query using LIMIT to get top 3 books

Write a SQL query using LIMIT 3 at the end to get the first 3 rows from sales ordered by copies_sold descending.

SQL

CREATE TABLE sales (book_title TEXT, copies_sold INTEGER);
INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 150),
('1984', 200),
('The Hobbit', 180),
('Pride and Prejudice', 120),
('To Kill a Mockingbird', 170);
-- We will order by copies_sold DESC in queries
SELECT TOP 3 book_title, copies_sold FROM sales ORDER BY copies_sold DESC;
SELECT book_title, copies_sold FROM sales ORDER BY copies_sold DESC LIMIT 3;
-- Your code here

Hint

Use LIMIT 3 at the end of the query to get the first 3 rows.

Practice

1. Which SQL clause is used to limit the number of rows returned in SQL Server?

easy

A. LIMIT

B. TOP

C. FETCH FIRST

D. ROWNUM

Solution

1. Step 1: Understand SQL Server syntax

   SQL Server uses the TOP clause immediately after SELECT to limit rows.

2. Step 2: Compare with other databases

   Other databases like MySQL and PostgreSQL use LIMIT at the end, not SQL Server.

3. Final Answer:

   TOP -> Option B

4. Quick Check:

   SQL Server = TOP [OK]

Hint: Use TOP right after SELECT in SQL Server [OK]

Common Mistakes:

• Using LIMIT in SQL Server
• Placing TOP at the end of the query
• Confusing FETCH FIRST with TOP

2. Which of the following is the correct syntax to get the first 5 rows in MySQL?

easy

A. SELECT * FROM table_name LIMIT 5;

B. SELECT TOP 5 * FROM table_name;

C. SELECT * FROM table_name FETCH FIRST 5 ROWS;

D. SELECT * FROM table_name WHERE ROWNUM <= 5;

Solution

1. Step 1: Recall MySQL syntax for limiting rows

   MySQL uses LIMIT at the end of the query to restrict rows.

2. Step 2: Check each option

   SELECT * FROM table_name LIMIT 5; uses LIMIT 5 correctly; SELECT TOP 5 * FROM table_name; uses SQL Server syntax; Options C and D are for other databases.

3. Final Answer:

   SELECT * FROM table_name LIMIT 5; -> Option A

4. Quick Check:

   MySQL = LIMIT at end [OK]

Hint: Use LIMIT at the end in MySQL queries [OK]

Common Mistakes:

• Using TOP in MySQL
• Placing LIMIT before FROM
• Using ROWNUM which is Oracle syntax

3. Given the table Employees with columns Id and Name, what will this query return in SQL Server?

SELECT TOP 3 Name FROM Employees ORDER BY Id DESC;

medium

A. The first 3 employees ordered by Id ascending

B. The last 3 employees ordered by Id descending

C. Syntax error due to TOP placement

D. The top 3 employees ordered by Id descending

Solution

1. Step 1: Understand TOP with ORDER BY in SQL Server

   The query selects the top 3 rows after ordering by Id descending, so highest Ids first.

2. Step 2: Analyze the result

   The result is the 3 employees with the highest Id values, ordered descending.

3. Final Answer:

   The top 3 employees ordered by Id descending -> Option D

4. Quick Check:

   TOP 3 + ORDER BY DESC = highest 3 rows [OK]

Hint: TOP limits rows after ORDER BY sorting [OK]

Common Mistakes:

• Thinking TOP returns first rows before ORDER BY
• Confusing ascending and descending order
• Assuming syntax error with TOP

4. Identify the error in this PostgreSQL query:

SELECT TOP 10 * FROM Customers;

medium

A. TOP is not valid in PostgreSQL

B. Missing ORDER BY clause

C. LIMIT should be placed before FROM

D. Syntax is correct

Solution

1. Step 1: Recall PostgreSQL syntax for limiting rows

   PostgreSQL uses LIMIT at the end, not TOP.

2. Step 2: Check the query

   The query uses TOP 10, which is invalid syntax in PostgreSQL and causes an error.

3. Final Answer:

   TOP is not valid in PostgreSQL -> Option A

4. Quick Check:

   PostgreSQL = LIMIT, not TOP [OK]

Hint: PostgreSQL uses LIMIT, not TOP [OK]

Common Mistakes:

• Using TOP in PostgreSQL
• Placing LIMIT before FROM
• Assuming ORDER BY is mandatory for LIMIT

5. You want to write a query that returns the top 5 highest salaries from an Employees table. Which query works correctly in both SQL Server and MySQL?

hard

A. Use UNION of SQL Server and MySQL queries

B. SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 5;

C. SELECT Salary FROM Employees ORDER BY Salary DESC FETCH FIRST 5 ROWS ONLY;

D. SELECT TOP 5 Salary FROM Employees ORDER BY Salary DESC;

Solution

1. Step 1: Understand syntax differences

   SQL Server uses TOP after SELECT; MySQL uses LIMIT at the end.

2. Step 2: Check each option for cross-DB compatibility

   SELECT TOP 5 Salary FROM Employees ORDER BY Salary DESC; works only in SQL Server; SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 5; only in MySQL; Use UNION of SQL Server and MySQL queries doesn't provide a single query that parses in both; SELECT Salary FROM Employees ORDER BY Salary DESC FETCH FIRST 5 ROWS ONLY; is standard SQL supported by both.

3. Final Answer:

   SELECT Salary FROM Employees ORDER BY Salary DESC FETCH FIRST 5 ROWS ONLY; -> Option C

4. Quick Check:

   FETCH FIRST = cross-DB standard [OK]

Hint: Use FETCH FIRST ROWS ONLY for cross-DB compatibility [OK]

Common Mistakes:

• Using LIMIT (MySQL only)
• Using TOP (SQL Server only)
• Proposing UNION which fails parsing