Several statements are used in PostgreSQL in order to make the query work and get our desired data from the database. The SELECT LIMIT statement is one of these statements. The whole statement is made up of two clauses/statements. The SELECT statement is used to get the data from one or more tables in the database and the LIMIT clause is optional but sometimes used with the SELECT statement to have a limit constraint on the number of rows returned by the SELECT statement.

In this article, we will see how SELECT LIMIT works in queries.

What Does the SELECT LIMIT Statement Do in PostgreSQL?

The SELECT LIMIT statement/command is used to get limited rows of specific data from one or multiple tables. The basic syntax for the SELECT LIMIT statement is given as follows:

SELECT col_list
 FROM tab_name
   [WHERE Condition]
   [ORDER BY sorting_order]
 LIMIT row_count;

In the above syntax:

● The SELECT statement is followed by the name of the column that we want the query to return.

● After the FROM statement, we specify the name of the table from where we want to get the data.

● The LIMIT clause is followed by the row count meaning the number of rows we want the query to return.

● We can optionally use the WHERE clause and the ORDER BY clause. The WHERE clause is followed by the condition that we want to impose on the data.

● The ORDER BY statement is also optional, we can get the data in a specific sorted order.

If the row count declared after the LIMIT statement is 0, the returning value will be an empty set. If the row count is NULL the query will return the same result as without a LIMIT statement.

Let’s move towards an example to get more clarity on the concept.

Example

To demonstrate the working of the SELECT LIMIT statement, consider a table named ”test_scores” having 4 columns candidate_is, candidate_name, candidate_gender, and candidate_scores. The table is given as:

Now if we want to get the female top 3 scorers we can write the query as:

SELECT *
 FROM test_scores
 WHERE candidate_gender = 'Female'
 ORDER BY candidate_score DESC
 LIMIT 3;

In the above query:

● We have selected all the columns from the table “test_scores” to return as a result of the query.

● We have imposed the condition that the query will return the rows where the candidate is female.

● The data is sorted in descending order.

● And lastly, the number of rows to be returned by the query is limited to 3 rows.

The output of the above query will be:

We can see that the output has returned the top 3 female scorers in the test. Similarly, we can customize the query for other conditions and and number of rows as well.

Sometimes we want to return the value from a specific offset, so we specify the offset in the query like this:

SELECT *
 FROM test_scores
 WHERE candidate_gender = 'Female'
 ORDER BY candidate_score DESC
 LIMIT 3 OFFSET 1;

In the above query, we have specified the offset as 1. Now what this query will do is, that it will function the same as above but it will return the data after the first position of the data, not the first record itself. So the output will be as follows:

We can see that the records returned by the query are now after the first position as we have declared the offset as 1.

So this is how the SELECT LIMIT statement works.

Conclusion

The SELECT LIMIT statement is used to limit data up to a specified number of rows. The number of rows has to be specified after the LIMIT keyword. We can specify some conditions or the sorting order of the data optionally. The resulting data will be limited to a specified number of rows.