Previous

SQL LIMIT

The LIMIT clause in SQL is used to specify the number of rows returned by a query. It's particularly useful when dealing with large datasets, and you want to limit the results to a specific number.

 

Basic Syntax of LIMIT

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
  • number_of_rows: The number of rows to be returned by the query.

 

Key Features of LIMIT Clause

1. Limiting the Number of Rows

The LIMIT clause restricts the number of rows returned. This is useful when you want only a subset of results.

SELECT name, city
FROM customers
LIMIT 5;

This query will return the first 5 rows from the customers table.

2. Combining LIMIT with ORDER BY

You can use LIMIT with the ORDER BY clause to get a specific number of results sorted in a particular order.

SELECT name, city
FROM customers
ORDER BY city ASC
LIMIT 3;

This will return the first 3 customers sorted by their city in ascending order.

3. Skipping Rows Using OFFSET

LIMIT can be combined with OFFSET to skip a certain number of rows and then return the next set of rows.

SELECT name, city
FROM customers
ORDER BY city ASC
LIMIT 5 OFFSET 10;

This query will skip the first 10 rows and return the next 5 rows from the customers table.

4. Limiting Rows in Combination with Aggregates

You can also use LIMIT with aggregate functions to get a specific number of results.

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city
LIMIT 3;

This query will return the first 3 cities with the total count of customers.

 

Example Queries

1. Get the First 10 Rows of Products

SELECT product_name, price
FROM products
LIMIT 10;

This query will return the first 10 rows from the products table.

2. Get the Top 5 Most Expensive Products

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

This will return the 5 most expensive products, sorted in descending order by price.

3. Skip the First 5 Rows and Return the Next 10

SELECT product_name, price
FROM products
LIMIT 10 OFFSET 5;

This will skip the first 5 products and return the next 10 from the products table.

4. Get the First 3 Customers from Each City

SELECT city, name
FROM customers
ORDER BY city, name
LIMIT 3;

This will return the first 3 customers for each city.

 

Important Notes

  • MySQL/PostgreSQL: In MySQL and PostgreSQL, the LIMIT clause is used to control the number of rows returned.
  • SQL Server: In SQL Server, TOP is used instead of LIMIT.
  • Performance: Using LIMIT can significantly improve performance, especially when dealing with large datasets, by limiting the number of rows returned to the user.

 

Summary

The LIMIT clause in SQL is a powerful tool to restrict the number of rows returned by a query. It is often used to limit results for pagination or when working with large datasets to improve performance. When combined with ORDER BY, OFFSET, or aggregates, it becomes even more versatile for various query needs.

Previous