PostgreSQL – ROW_NUMBER Function
In PostgreSQL, the ROW_NUMBER() function is used to assign a unique integer to every row that is returned by a query.
Syntax:
ROW_NUMBER() OVER(
[PARTITION BY column_1, column_2, …]
[ORDER BY column_3, column_4, …]
)
Let’s analyze the above syntax:
- The set of rows on which the ROW_NUMBER() function operates is called a window.
- The PARTITION BY clause is used to divide the query set results.
- The ORDER BY clause inside the OVER clause is used to set the order in which the query result will be displayed.
Example 1:
First, create two tables named mammals and Animal_groups:
CREATE TABLE Animal_groups (
animal_id serial PRIMARY KEY,
animal_name VARCHAR (255) NOT NULL
);
CREATE TABLE Mammals (
mammal_id serial PRIMARY KEY,
mammal_name VARCHAR (255) NOT NULL,
lifespan DECIMAL (11, 2),
animal_id INT NOT NULL,
FOREIGN KEY (animal_id) REFERENCES Animal_groups (animal_id)
);
Now add some data to it:
INSERT INTO Animal_groups (animal_name)
VALUES
('Terrestrial'),
('Aquatic'),
('Winged');
INSERT INTO Mammals(mammal_name, animal_id, lifespan)
VALUES
('Cow', 1, 10),
('Dog', 1, 7),
('Ox', 1, 13),
('Wolf', 1, 11),
('Blue Whale', 2, 80),
('Dolphin', 2, 5),
('Sea Horse', 2, 3),
('Octopus', 2, 8),
('Bat', 3, 4),
('Flying Squirrels', 3, 1),
('Petaurus', 3, 2);
In the following query, we change the column in the ORDER BY clause to mammal_name, the ROW_NUMBER() function assigns the integer values to each row based on the mammal name order.
SELECT
mammal_id,
mammal_name,
animal_id,
ROW_NUMBER () OVER (
ORDER BY mammal_name
)
FROM
Mammals;
Output:
Example 2:
The following query uses the ROW_NUMBER() function to assign integers to the distinct prices from the products table:
SELECT DISTINCT
lifespan,
ROW_NUMBER () OVER (ORDER BY lifespan)
FROM
Mammals
ORDER BY
lifespan;
Output:
Last Updated :
16 Feb, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...