PostgreSQL or Postgres is a feature-enrich relational database that enables users to save/store their data securely. The data is stored in the form of tables and can be manipulated or fetched whenever needed. For this purpose, the SELECT statement is used in Postgres. By default, the SELECT statement retrieves all rows of the selected table. But there might be scenarios where users have to extract only limited rows/ records. Well! To deal with such scenarios! Postgres offers a LIMIT clause that allows fetching a limited set of rows instead of an entire table.
Quick Outline
This blog will illustrate the use of the LIMIT clause in Postgres using the following outlines:
- What is LIMIT Clause and How Does it Work in PostgreSQL?
- Case 1: Basic Usage of LIMIT Clause
- Case 2: LIMIT Clause With OFFSET
- Case 3: LIMIT Clause With ORDER BY
- Case 4: Fetching the Last N Rows of the Table Using the LIMIT Clause
- Case 5: LIMIT Clause With WHERE
- Case 6: LIMIT Clause With RANDOM()
What is LIMIT Clause and How Does it Work in PostgreSQL?
LIMIT is an optional clause in PostgreSQL that is executed with a SELECT query to fetch only specific records from a table. The most frequent use cases of the LIMIT clause are fetching Top N or Last N records from any given table. It can also be utilized to fetch a limited number of random rows/records from a table. The basic syntax for executing the LIMIT clause is depicted below:
LIMIT num_of_rows;
Where num_of_rows indicates the number of rows/records to be fetched from a result set.
Case 1: Basic Usage of LIMIT Clause
Let’s first fetch the result set upon which we are going to implement the Postgres LIMIT clause:
SELECT * FROM attendance;
A table named “attendance” with three columns “employee_id”, “employee_name”, and “employee_ranking” are fetched using the SELECT query:
The attendance table contains a total of 8 records. Suppose we need only the top 5 records of the attendance table. To do that, we will use the SELECT statement with the LIMIT clause as follows:
SELECT * FROM attendance
LIMIT 5;
The LIMIT clause is followed by “5”, which means only five records will be retrieved from the “attendance” table as shown in the following screenshot:
The result set contains only five records as expected.
Case 2: LIMIT Clause With OFFSET
Do you want to get a limited set of records from a particular position? If yes! Execute the LIMIT clause with the combination of the OFFSET clause. The OFFSET clause assists us when we have to skip some records from the start of the table.
To be more precise, to get the limited set of rows from any particular position define/specify the number of rows to be fetched in the LIMIT clause and the number of rows/records to be omitted in the OFFSET clause. Here is how the combination of LIMIT and OFFSET clauses work in Postgres:
SELECT * FROM attendance
LIMIT 5 OFFSET 2;
Here “Limit 5” instructs Postgres to fetch only five records of the “attendance” table. While the “OFFSET 2” states that skip the first two records of the selected table. All in all, the first two records will be skipped, and the subsequent five records will be fetched:
Case 3: LIMIT Clause With ORDER BY
The ORDER BY clause can be used along with the LIMIT clause to fetch the limited set of records from a table based on the sorting order of a specific column. For instance, in the following query, the top five records based on the employee_ranking column will be fetched:
SELECT * FROM attendance
ORDER BY employee_ranking ASC
LIMIT 5;
First, the result set is sorted into ascending order based on the employee_ranking column. After that, the LIMIT clause is utilized to fetch the first five rows/records from the sorted table.
Case 4: Fetching the Last N Rows of the Table Using the LIMIT Clause
By default, the LIMIT clause fetches the records from the top/start of the table. To get the last N rows/ records, sort the result set in descending order and then apply the LIMIT clause on the descendingly sorted table.
SELECT * FROM attendance
ORDER BY employee_ranking DESC
LIMIT 3;
The above query fetches the last three records of the “attendance” table. The records will be retrieved based on the employee_ranking column:
Case 5: LIMIT Clause With WHERE
By using the LIMIT clause with the WHERE clause, you can retrieve a limited set of records based on a certain condition. Here is how you can do this:
SELECT * FROM attendance
WHERE employee_id >=7
ORDER BY employee_ranking DESC
LIMIT 3;
This code is the same as the previous one; the only difference is the “WHERE” clause. A condition is specified using the WHERE clause that must be fulfilled. Therefore, the above query will fetch the last three records of the attendance table, but they must have an employee_id greater than or equal to 7.
We fetched the last three records of the table but the resultant table contains only two records. This means that no other record fulfills the stated condition. Therefore, the query retrieved the last two records instead of three.
Case 6: LIMIT Clause With RANDOM()
As of now, we have witnessed that the LIMIT clause retrieves a limited subset of sorted/sequenced rows. However, we can also fetch a limited number of random records from a table by executing the LIMIT clause alongside the RANDOM() function.
Here is a practical demonstration of the LIMIT clause with the RANDOM() function:
SELECT * FROM attendance
ORDER BY RANDOM()
LIMIT 4;
The above query will sort the “attendance” table randomly and then fetch the four rows from it:
The result shows that the query retrieves four random rows from the attendance table.
Final Thoughts
In PostgreSQL, the LIMIT clause is utilized with the SELECT query to fetch the limited rows/records from the selected table. It retrieves a subset of records from the start or bottom of the result set. To get a subset of rows/records from a specific position the LIMIT clause is executed with the OFFSET clause.
The LIMIT and WHERE clauses can be used combinedly to fetch the limited subset of rows based on a particular condition. Other than that, the LIMIT clause can be executed with the RANDOM() function to get a limited number of random records from a complete result set.
All these use cases are practically implemented in Postgres and the screenshots of each case/example are provided for better understanding.
