ROW_NUMBER() OVER PARTITION BY IN SQL

ROW_NUMBER()

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

PARTITION BY value_expression

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Select * from Row_Number (table name)

Fname	Salary	State
Vipin	10000	Delhi
Chandra	9000	Delhi
Taslim	8000	UP
Raghu	12000	UP
Ravi	6000	MP
Jatin	7000	MP
Neeraj	16000	AP
Manish	5000	Delhi
Vijay	23000	Delhi

SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS Row,Fname,State,Salary from Row_Number 

Row	Fname	State	Salary
1	Vijay	Delhi	23000
2	Neeraj	AP	16000
3	Raghu	UP	12000
4	Vipin	Delhi	10000
5	Chandra	Delhi	9000
6	Taslim	UP	8000
7	Jatin	MP	7000
8	Ravi	MP	6000
9	Manish	Delhi	5000

SELECT ROW_NUMBER() OVER(ORDER BY Salary ASC) AS Row,Fname,State,Salary from Row_Number 

Row	Fname	State	Salary
1	Manish	Delhi	5000
2	Ravi	MP	6000
3	Jatin	MP	7000
4	Taslim	UP	8000
5	Chandra	Delhi	9000
6	Vipin	Delhi	10000
7	Raghu	UP	12000
8	Neeraj	AP	16000
9	Vijay	Delhi	23000

Select Fname,State,Salary, ROW_NUMBER() OVER (PARTITION BY State Order by salary desc) as row from Row_Number 

Fname	State	Salary	row
Neeraj	AP	16000	1
Vijay	Delhi	23000	1
Vipin	Delhi	10000	2
Chandra	Delhi	9000	3
Manish	Delhi	5000	4
Jatin	MP	7000	1
Ravi	MP	6000	2
Raghu	UP	12000	1
Taslim	UP	8000	2