Sql Server ROW_NUMBER

ROW_NUMBER number generate output automatically in the query output a sequential number in each row separated by partition.Every partition has separate row number as per order by clause. You can use ROW_NUMBER both ways without or with partition by. 

We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

       id INT NOT NULL,

       name VARCHAR (500) NOT NULL,

       age INT NOT NULL,

       adress VARCHAR (500) NOT NULL,

       date_of_birth DATETIME NOT NULL,

       height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'44,student street','11/08/2018',16

1)Here is example of two number without  partition by
 

SELECT name,

       age, 

       adress,

       date_of_birth,

       height , 

       row_number() over (order by name) as RowNum 

       FROM student

Output

name    age   adress            date_of_birth         height  RowNum   
John1   12    34,student street    2018-01-08 00:00:00.000    10.50    1
John10  15    43,student street    2018-10-08 00:00:00.000    15.50    2
John11  16    44,student street    2018-11-08 00:00:00.000    16.00    3
John2   13    35,student street    2018-02-08 00:00:00.000    11.00    4
John3   12    36,student street    2018-03-08 00:00:00.000    11.50    5
John4   13    37,student street    2018-04-08 00:00:00.000    12.00    6
John5   12    38,student street    2018-05-08 00:00:00.000    12.50    7
John6   13    39,student street    2018-06-08 00:00:00.000    13.50    8
John7   15    40,student street    2018-07-08 00:00:00.000    14.00    9
John8   14    41,student street    2018-08-08 00:00:00.000    14.50    10
John9   12    42,student street    2018-09-08 00:00:00.000    15.00    11


2)Here is the example of row number with partition by 

SELECT name, 

       age,

       adress,

       date_of_birth,height , 

       row_number() OVER(PARTITION BY name ORDER BY age ASC)

FROM student

Output

name    age   adress            date_of_birth         height  RowNum   
John1    12    34,student street    2018-01-08 00:00:00.000    10.50    1
John10    15    43,student street    2018-10-08 00:00:00.000    15.50    1
John11    16    44,student street    2018-11-08 00:00:00.000    16.00    1
John2    13    35,student street    2018-02-08 00:00:00.000    11.00    1
John3    12    36,student street    2018-03-08 00:00:00.000    11.50    1
John4    13    37,student street    2018-04-08 00:00:00.000    12.00    1
John5    12    38,student street    2018-05-08 00:00:00.000    12.50    1
John6    13    39,student street    2018-06-08 00:00:00.000    13.50    1
John7    15    40,student street    2018-07-08 00:00:00.000    14.00    1
John8    14    41,student street    2018-08-08 00:00:00.000    14.50    1
John9    12    42,student street    2018-09-08 00:00:00.000    15.00    1