How to use of Row_Number function in SQL Queries

Row_Number in SQL is used to return the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Row_Number assigns a unique number to the rows defined in the Partition by clause. These rows are ordered in the sequence of the column mentioned in the order by clause.

Syntax of Row_Number():

Row_number() over(partition by col1, col2, .. order by col3 desc)

Example:

Suppose you have a Employee  table as:

create table Employee

(

Emp_Id int,

Emp_name varchar2(20),

Salary  int

);

 Insert some data in table.

Like this. Ex:

insert into Employee values(1, Ajay, 25000);

Suppose we want to select the employees with salary greater than 20000.

 We can query the table using row_number and get assign number to each row as follows:

Select  row_number() over (order by salary DESC) as Serial_no,* From Employee where salary>20000;

 Now we can see that all the rows with row_number values as 1 are the ones with highest salary.