Limiting the Number of Rows (LIMIT) and Managing Results

How do you limit the number of rows returned by a query? If you only need the first 5 students, the employee with the highest salary, or a top-10 products list, then the LIMIT command is exactly what you need!

When you’re working with databases, your query results can be huge. In these cases, you’ll want to limit the amount of data returned so you can:

1. Reduce the load on the server and network.
2. Make it easier to analyze data without scrolling through endless pages.

Here’s a simple analogy: imagine you wanted to buy a drink for a girl at a bar, but accidentally paid for drinks for everyone there. Sure, that’s impressive, but do you really need that many girls? That’s why we learn to order just what we need, and LIMIT helps us do that.

Basic LIMIT Syntax

Here’s how it looks:

SELECT column1, column2
FROM table
LIMIT number_of_rows;

Example 1: Limiting records

Let’s say we have a students table with 1000 rows. We want to see just the first 3 records:

SELECT *
FROM students 
LIMIT 3;

Result:

Easy: instead of a long list, we see just three rows. LIMIT returned exactly as many rows as we asked for.

Practical Uses for LIMIT

Example 2: Top products

You’re working with a products table and want to know the names and prices of the five most expensive items. First, we sort the records by price descending, then limit the result:

SELECT product_name, price
FROM products 
ORDER BY price DESC 
LIMIT 5;

Result:

Now you see only the top 5 products with the highest price.

How LIMIT Helps You Test Queries

When you’re writing SQL queries, you’ll often deal with big amounts of data. LIMIT lets you first check if your query is correct by returning a small chunk of data. For example:

SELECT *
FROM transactions_log
LIMIT 10;

Once you’re sure your query logic is right, you can remove the LIMIT.

Things to Watch Out for with LIMIT

1. LIMIT without ORDER BY

If you don’t specify ORDER BY, LIMIT will return data in a random order — or more accurately, in whatever order it’s stored in the table. Usually that’s fine, but sometimes it can be unpredictable.

For example:

SELECT name, age
FROM students 
LIMIT 3;

Without ORDER BY, you can’t be sure which three records you’ll get.

To avoid this, use ORDER BY:

SELECT name, age
FROM students 
ORDER BY age DESC 
LIMIT 3;

2. Using with WHERE

You can combine WHERE to filter rows and LIMIT to restrict the result. This is handy when there’s too much data and you only care about the first matches.

SELECT *
FROM students 
WHERE age > 18 
LIMIT 5;

This query will return the first 5 students older than 18.

3. Real-life use: viewing the first page of data

If you’re working with a data display system (like a web app), LIMIT can be used to show the first page of data.

SELECT *
FROM employees 
ORDER BY hire_date 
LIMIT 10;

Here you’ll get the first 10 employees, sorted by hire date.

Advanced Example: Combining LIMIT with Aggregates

Imagine you want to show the three most popular product categories:

SELECT category, COUNT(*) AS total_products
FROM products 
GROUP BY category 
ORDER BY total_products DESC 
LIMIT 3;

This query first groups products by category, sorts them by count, and then keeps just three rows.

You’ll learn exactly how the GROUP BY operator and COUNT() function work in the next lectures.

Common Mistakes When Using LIMIT

When working with LIMIT, you might run into a few issues:

"Random row order": if you forget ORDER BY, your data might come in an unexpected sequence. Always specify the order if it matters.

"Cutting off important data": for example, if two records have the same value in the sort column, LIMIT might return an incomplete result. Add extra sort criteria to avoid this.

SELECT *
FROM students 
ORDER BY age DESC, name ASC 
LIMIT 5;

"Wrong combo with WHERE". Sometimes your filter conditions narrow the selection so much that LIMIT is pointless. For example, if WHERE returns only 2 records and LIMIT is set to 10, you’ll still get just 2 rows.

"Database dependency". Some DBMSs have alternative syntax for limiting rows (like TOP in SQL Server). PostgreSQL uses LIMIT.

Why Do You Need LIMIT in Real Life?

Interfaces with lots of records: imagine an online store where you browse products page by page. The first page might show only 10 records, and LIMIT makes that possible.

Reports: often you only care about the top part of the data (like top-5 employees, sales leaders, students with the highest grades).

Testing and query optimization: instead of working with millions of rows, you can quickly test your query on a small sample.

2

Task

SQL SELF, level 3, lesson 0

Locked

Limiting the number of records in a table

Limiting the number of records in a table

Open

2

Task

SQL SELF, level 3, lesson 0

Locked

Display the Top 5 Employees by Salary

Display the Top 5 Employees by Salary

Open