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