Easiest Explanation Row_number in SQL / PostgreSQL

Row_number in SQL is a window function which basically generates a unique positive integer number against a every rows of a table. It is widely used in SQL irrespective of type of databases, whether it is a SQL server, Mysql, PostgreSQL etc etc.

Use Case of ROW_NUMBER :

Whenever you need to get a unique identity of a row you can use this window function to get a row number and based on that row number you can perform your task. It generates on run time and will not be saved in your table.

We can use row_number in deduplication also. Here is an example.

Syntax of ROW_NUMBER in SQL :

ROW_NUMBER() OVER (
[PARTITION BY partition_expression, … ]
ORDER BY sort_expression [ASC | DESC], … )

Reference for syntax

Example :

Here, for example I have a table named “students” having column “id” & “name” performed some operations. Queries are given below :-

1)       select id, name, row_number() over( order by id ) from students;


2)       select id, name, row_number() over( partition by name ) from students;

From query 1 we are just getting the data with row number as id in ascending order.

From query 2 we are getting row number partitioned by name where , wherever new name( partition) getting it is start printing new row number.

A youtube video describing row_number in sql in hindi