# PostgreSQL ROW_NUMBER() Function With Examples

> In Postgres, the “ROW_NUMBER()” function is used with the OVER clause to operate on a set of rows and assigns a unique integer to each row.

PostgreSQL provides a built-in Window function named “ **ROW_NUMBER()** ” that operates on a set of rows and assigns a unique integer to each row. The set/collection of records is referred to as a “Window”. The ROW_NUMBER() function assigns the consecutive numbering/ranking to the rows which ultimately assists us in data analysis and manipulation.

This write-up presents a comprehensive guide on the usage of the ROW_NUMBER() function using suitable examples.

 **How to Use the ROW_NUMBER() Function in Postgres?**

In Postgres, “ROW_NUMBER()” is used with the “OVER” clause to get the row number, as depicted in the following syntax:
    
    
    ROW_NUMBER() OVER (
    [PARTITION BY col_list ] 
    [ORDER BY col_list]
    );

\- In Postgres, a “Window” is referred to as the set of rows on which ROW_NUMBER() is applied.  
\- The “PARTITION BY” clause is optional and can be used to split the “window” into partitions or groups.  
\- If the “PARTITION BY” clause is omitted, then the ROW_NUMBER() considers the whole window as one partition.  
\- "ORDER BY" specifies the order in which the numbers will be assigned.

 **Example 1: How Does ROW_NUMBER() Work in Postgres?**

In this example, we will utilize a “programming_languages” table that we have already created in our database:
    
    
    SELECT *,
    ROW_NUMBER() OVER (
    PARTITION BY language
    ORDER BY id DESC
    )
    FROM programming_languages;

In this code:

\- The “ROW_NUMBER()” function is used with the “OVER” clause to get the row number within the associated partition.  
\- The “PARTITION BY” clause is used to split the table by language.  
\- The "ORDER BY" clause organizes the partitions descendingly.

From the output, it is clear that the consecutive ranking has been assigned to the given table.

 **Example 2: ROW_NUMBER() With Subquery**

This example explains how to use the ROW_NUMBER() function with the subquery to get the list of unique records:
    
    
    SELECT DISTINCT *,
    ROW_NUMBER() OVER (
    ORDER BY language
    )
    FROM (
    SELECT DISTINCT language
    FROM programming_languages
    ) 
    programming_languages;

In the above-stated code block:

\- A subquery can be used with the DISTINCT operator to get the unique records.  
\- After that, the ROW_NUMBER() can be utilized in the outer query to assign the numbering to the unique records:

The output shows that the numbering has been assigned to the unique “languages”.

 **Note:** The ROW_NUMBER() works on the result set prior to the DISTINCT clause. Therefore, using a DISTINCT operator with the ROW_NUMBER() wouldn’t exclude the duplicates.

 **Example 3: ROW_NUMBER() for Pagination**

In Postgres, a technique named pagination is used to retrieve chunks of records instead of showing all records of a result set. Usually, the LIMIT clause is used to get the limited data, however, the ROW_NUMBER() can also be used as its alternate. Here is an example:
    
    
    SELECT * FROM
    (SELECT *,
    ROW_NUMBER() OVER (
    ORDER BY language)
    FROM programming_languages
    )
    programming_languages
    WHERE id BETWEEN 4 AND 7;

This query retrieves the records between id 4 and 7 and assigns them consecutive row numbers:

That's all from this Postgres guide on ROW_NUMBER() function.

 **Conclusion**

In PostgreSQL, a built-in Window function named “ **ROW_NUMBER()** ” is used with the OVER clause to operate on a set of rows and assigns a unique integer to each row. The “PARTITION BY” clause is optional and can be used with the ROW_NUMBER() to split the “window” into partitions or groups. If the "PARTITION BY" clause is skipped, the whole window will be treated as a single partition. This post provided a thorough guide on how to use the ROW_NUMBER() function in Postgres.

---
[View this page online](https://www.commandprompt.com/education/postgresql-row_number-function-with-examples/)