When working with databases, you often don’t want to retrieve all the records at once—especially in large datasets. That’s where the LIMIT clause in SQL comes in. It lets you control how many rows are returned from a query.
In this article, you’ll learn:
-
What
LIMITdoes in SQLite -
How to use
LIMITin Python with SQLite -
How to combine
LIMITwithORDER BYandOFFSET -
Real-world examples and best practices
✅ What Is LIMIT in SQL?
The LIMIT clause restricts the number of rows returned by a SELECT query.
Syntax:
SELECT columns FROM table_name LIMIT number;
For example:
SELECT * FROM Users LIMIT 5;
→ This returns only the first 5 records from the Users table.
Step 1: Connect to SQLite in Python
Let’s start by setting up our database connection:
import sqlite3
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
Step 2: Select Data with LIMIT
Example: Get the first 3 users from the Users table:
cursor.execute("SELECT * FROM Users LIMIT 3")
rows = cursor.fetchall()
for row in rows:
print(row)
fetchall()retrieves all rows returned by the query—LIMITensures that’s no more than 3.
Step 3: Use LIMIT with ORDER BY
Often, you'll want to sort your results before limiting them.
cursor.execute("SELECT * FROM Users ORDER BY age DESC LIMIT 2")
rows = cursor.fetchall()
for row in rows:
print(row)
This retrieves the 2 oldest users by sorting the age column in descending order.
Step 4: Use OFFSET for Pagination
You can skip rows using OFFSET:
cursor.execute("SELECT * FROM Users ORDER BY id LIMIT 2 OFFSET 2")
rows = cursor.fetchall()
This skips the first 2 rows and returns the next 2, useful for pagination.
Full Example: LIMIT in Python with SQLite
import sqlite3
# Connect to database
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
# Get top 3 users by age
cursor.execute("SELECT * FROM Users ORDER BY age DESC LIMIT 3")
top_users = cursor.fetchall()
print("Top 3 oldest users:")
for user in top_users:
print(user)
# Paginate: Get next 3 users after skipping 3
cursor.execute("SELECT * FROM Users ORDER BY age DESC LIMIT 3 OFFSET 3")
next_users = cursor.fetchall()
print("\nNext 3 users:")
for user in next_users:
print(user)
conn.close()
Tips for Using LIMIT Effectively
| Tip | Why It’s Useful |
|---|---|
✅ Combine with ORDER BY |
Makes the returned results predictable |
✅ Use OFFSET for pagination |
Helps in building page-wise navigation |
| ✅ Profile queries for performance | Prevents over-fetching large datasets |
✅ Use LIMIT 1 with WHERE for fast lookups |
Efficient way to retrieve a single row |
⚠️ Common Pitfalls
| Problem | Cause | Solution |
|---|---|---|
| Results vary between runs | No ORDER BY clause |
Always use ORDER BY for consistent ordering |
| OFFSET skips too few/many rows | Misunderstood zero-based indexing | OFFSET 0 starts at the first row |
| Too many rows returned | LIMIT not used or wrong number | Double-check query logic and parameter values |
| Poor performance on large OFFSET | OFFSET skips rows internally | Consider using indexed values (e.g., id > x) for better performance |
Best Practices
-
Paginate large tables using
LIMIT+OFFSETto avoid overloading memory. -
Use indexes on columns used with
ORDER BYandWHEREto speed up queries. -
Avoid huge OFFSETs in large tables—consider using cursors or key-based pagination.
-
When fetching only one result (e.g., latest record), use
LIMIT 1for efficiency.
Conclusion
Using LIMIT with Python and SQLite is a powerful way to control query results. It helps improve performance, supports pagination, and keeps your app responsive—even with large datasets.
With just a few lines of code, you can fetch exactly the rows you need and nothing more. Combined with ORDER BY and OFFSET, LIMIT becomes an essential tool in your SQL toolbox.
