The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement. However, for most relational databases (DBMSes), including MySQL/MariaDB, PostgreSQL, and Oracle, the SQL LIMIT clause can solve several problems. In today's blog, we'll explore a few of these, using Navicat for PostgreSQL.
Keeping Result Sets Manageable
In many production and test databases, table sizes routinely reach millions of rows and have dozens of columns. For that reason, it's never a good idea to run SELECT * queries against your database(s). Keeping the results down to one hundred or one thousand helps keep result sets down to a size that's more easily digestible.
Navicat development and administration tools automatically limit result sets by default in order to prevent straining your database server(s). You can see it in action when you open a table. At the bottom of the application window, the SQL that Navicat executed to fetch the table rows is displayed. It ends with the "LIMIT 1000 OFFSET 0", which means that only the first 1000 records are displayed.
You can change the default number of records to show or turn off limiting entirely on the RECORDS Options screen:
Top N Queries
As the name implies, top-N queries are those that attempt to find the top number of records from a result set. This could be top 1, top 3, top 5, top 10, or top [any] number. Some common examples are:
- Find the top 10 highest paid employees
- Find the top 20 most profitable customers
- Find the top 3 users on the system
These queries are hard to do with just an ORDER BY and WHERE clause alone, but not using the LIMIT clause. Here's an example:
Top 5 Unique Job IDs
Let's say that we wanted to find the top unique Job IDs in a table. Here's a query that does just that:
The DISTINCT keyword makes sure that duplicate IDs are removed from the results.
Closest Rows to a Given Date
It is possible to locate rows closest to a given date using LIMIT. You just have to compare row dates to the given date, order the results, and limit the results to the number of rows that you'd like to see. Here's a query that returns rows whose creation_date is greater than '2018-01-01':
In this case, 2018-01-02 was the closest later date.
Bottom N Queries
The corollary of top N queries are bottom N queries. These are queries that attempt to find the bottom number of records from a result set. We can convert our Top queries into their Bottom equivalents quite easily!
Bottom 5 Unique Job IDs
To return the bottom 5 unique job IDs, all you need to do is remove the DESC modifier in the ORDER BY clause. That will order records in ascending (ASC) order, as is default:
Closest Rows below a Given Date
Locating the closest rows before a given date is likewise fairly easy. We just need to change the greater than '>' operator to less than '<' and reorder results in descending (DESC) order:
Conclusion
In today's blog, we explored a few uses for the LIMIT clause, using Navicat for PostgreSQL. Like to give Navicat for PostgreSQL a try? You can evaluate it for 14 days completely free of charge!
