EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ROW_NUMBER
 

PostgreSQL ROW_NUMBER

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 10, 2023

PostgreSQL ROW_NUMBER

 

 

Introduction to PostgreSQL ROW_NUMBER

The PostgreSQL ROW_NUMBER() function is a windows function. The ROW_NUMBER() function manipulates the set of rows, and the row’s set is termed as a window. We can use the PARTITION BY clause with the ROW_NUMBER() function, which is optional; if we have defined it, then it handles the set of rows or window like splitting the set of rows into subsets if the PARTITION BY clause is not defined, then the entire result set of rows considered as the single partition. Also, we can use the ORDER BY clause with the ROW_NUMBER() function to order the rows.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

Consider the following syntax:

ROW_NUMBER()
OVER
(
[PARTITION BY column_name_1, column_name_2,…]
[ORDER BY column_name_3,column_name_4,…]
)

Explanation:

PARTITION BY: This is an optional clause in the case of the ROW_NUMBER() function. The PostgreSQL ROW_NUMBER() divides a set of rows into partitions or smaller sets.

ORDER BY: This defines how the order of the numbers should be assigned in the OVER clause.

How does the PostgreSQL ROW_NUMBER function work?

The ROW_NUMBER() function operates on a set of rows termed s a window.mIf the PARTITION BY clause is specified, then the row number will increment by one and start with one.

If we have not specified then PARTITION BY clause, then the ROW_NUMBER function will consider the entire window or set of results as a single partition.

We will create tables named ‘category’ and ‘items’ to understand the examples of the PostgreSQL ROW_NUMBER() function in detail.

Consider the following CREATE TABLE statement to create the category and items tables.

CREATE TABLE category

Code:

(
category_id serial PRIMARY KEY,
category_name VARCHAR(80) NOT NULL
);

CREATE TABLE items

Code:

(
item_id serial PRIMARY KEY,
item_name VARCHAR(80) NOT NULL,
item_price numeric,
category_id numeric
);

Now, we will insert some data in the ‘category’ and ‘items’ tables by using the INSERT TABLE statement:

Code:

INSERT INTO category(category_name) values
('furniture'),
('Electronics'),
('Cloths');

Output:

select * from category;

Illustrate the result of the above statement by using the following snapshot and SQL statement.

PostgreSQL ROW_NUMBER1

 Code:

INSERT INTO items(item_name,item_price,category_id)
VALUES ('Chair',800,1),
('Table',1200,1),
('Sofa',8000,1),
('Bed',8000,1),
('Mobile',12000,2),
('Laptop',30000,2),
('Charger',1200,2),
('Cable',1200,2),
('Pant',1700,3),
('Shirt',1300,3),
('Inner wear',300,3),
('Top wear',300,3);

 Output:

select * from items;

Illustrate the result of the above statement by using the following snapshot and SQL statement.

PostgreSQL ROW_NUMBER2

Examples of implementing the ROW_NUMBER function in PostgreSQL

Below are the examples mentioned:

Example #1

Code:

SELECT
item_id,
item_name,
category_id,
ROW_NUMBER () OVER (ORDER BY item_id)
FROM
items;

Output:

PostgreSQL ROW_NUMBER3

Explanation: In the above example, we have not defined the PARTITION BY clause, which results in the entire result as a single PARTITION in the ROW_NUMBER() function. We have defined the ORDER BY clause, which in result sorts the result set by item_id. The PostgreSQL ROW_NUMBER() function assigns numeric values based on the item_id order for each row.

Example #2

Consider the following statement where we use the item_name in the ORDER BY clause.

So for each row, the numbers are assigned as per the item name order.

Code:

SELECT
item_id,
item_name,
category_id,
ROW_NUMBER () OVER (
ORDER BY item_name
)
FROM
items;

Output:

Category id

Example #3

Consider the following statement where we will use the PARTITION BY clause on the category_id column, which will divide the result set into partitions based on the values of the category_id column. Each row of the partition starts with one and then increases by one for the remaining rows in the same partition. As per the values in the item_name column, the PostgreSQL ORDER BY clause sorts the rows in every partition.

Code:

SELECT
item_id,
item_name,
category_id,
ROW_NUMBER () OVER (
PARTITION BY category_id
ORDER BY item_name
)
FROM
items;

Output:

PostgreSQL ROW_NUMBER5

Example #4

DISTINCT operator with the PostgreSQL ROW_NUMBER() function

For each distinct row in the items table, the ROW_NUMBER() function assigns a number.

Code:

SELECT DISTINCT
item_price,
ROW_NUMBER () OVER (ORDER BY item_price)
FROM
items
ORDER BY
item_price;

Output:

DISTINCT operator

Example #5

We can use the pagination technique to display the subset of rows. There are various methods to achieve the Pagination, like using the LIMIT clause or the use of the ROW_NUMBER() function. Consider the following statement to select the 4 rows starting at row index 5:

Code:

SELECT
*
FROM
(
SELECT
item_id,
item_name,
item_price,
ROW_NUMBER () OVER (ORDER BY item_name)
FROM
items
) x
WHERE
ROW_NUMBER BETWEEN 5 AND 8;

Output:

Pagination

Advantages of using the ROW_NUMBER function in PostgreSQL

  • This function is used to generate the sequential numbers on the fly.
  • This function is used to perform pagination.
  • We can find out the duplicate rows by using this function.
  • We can find rows from a range of rows using the PostgreSQL ROW_NUMBER function.
  • This function is used to sort rows.

Conclusion

From the above article, we hope you understand how to use the PostgreSQL ROW_NUMBER() function and how the PostgreSQL ROW_NUMBER() function works. Also, we have added some examples of this function to understand it in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL ROW_NUMBER” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL ROLLUP
  2. PostgreSQL Alias
  3. Guide to PostgreSQL Queries
  4. List of Operators of SQL REGEXP

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW