In PostgreSQL, a LIMIT clause allows us to get/fetch the top n rows. The LIMIT clause allows us to extract a subset of rows from a resultant table returned by a query. LIMIT is an optional clause in Postgres; however, if we have to fetch the first N rows, then the LIMIT clause must be used.

This post will explain how to get the first N rows in PostgreSQL through practical examples. So, let’s begin.

How to Get/Fetch Top N Rows in Postgres?

As the name suggests, the LIMIT clause is used to fetch limited records from a result set. The below snippet demonstrates the basic syntax of the LIMIT clause:

SELECT col_list 
FROM tab_name
ORDER BY ASC | DESC
LIMIT no_of_rows;

Let’s comprehend the above syntax stepwise:

- col_list represents a single or multiple columns to be selected.

- tab_name represents a table from which the data will be fetched.

- ORDER BY is an optional clause that sorts the table’s records in a particular order.

- The LIMIT clause determines how many rows to fetch.

Example #1: How to Fetch Top Five Rows of a Table in Postgres?

We have a table named “article_details” that consists of three columns: article_id, article_title, and published_date. Let’s run the SELECT statement to fetch the table’s data:

SELECT *
FROM article_details;

The article_details table has twelve unsorted records. Suppose we want to get the top five articles regarding their ids. To achieve this, let’s run the below query:

SELECT *
FROM article_detail
ORDER BY article_id ASC
LIMIT 5;

The LIMIT clause succeeded in fetching the top five records with respect to the article_id column.

Example #2: How to Fetch Top Five Recently Published Articles?

Consider the same article_details table having three columns as shown in the previous example. This time we need to fetch the top five articles concerning their published date:

SELECT *
FROM article_details
ORDER BY published_date DESC
LIMIT 5;

Since we want to fetch the top five recently published articles, so we sorted the published_date column in descending order. Next, we utilized the LIMIT clause to specify the number of records to be fetched:

Notice that the LIMIT clause successfully fetched the top five recently published articles.

That was all the basics regarding how to get top N rows in PostgreSQL. You can get detailed information about the LIMIT clause from this article.

Conclusion

To get the top n rows of a table, the LIMIT clause is used in PostgreSQL. The LIMIT clause allows us to extract a subset of rows from a resultant table returned by a query. It is an optional clause; however, if we have to fetch the first N rows, then the LIMIT clause must be used. This article uses a couple of examples to demonstrate how to get top N rows in PostgreSQL using the LIMIT clause.