Ranking Function – Row_Number


Ranking functions are functions that allow you to sequentially number your result set.

Syntax

ROW_NUMBER ( ) OVER ([ ] )

partition_by_clause is a column or set of columns used to determine the grouping in which the ROW_NUMBER function applies sequential numbering.

order_by_clause is a column or set of columns used to order the result set within the grouping.

Examples

CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')


Sample - 1
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
EmpName,
Age
FROM Emp
Sample - 2
SELECT ROW_NUMBER() OVER (ORDER BY Age desc) AS [Row Number by Age],
EmpName,
Age
FROM Emp

Row Number by Age EmpName Age
——————– ——— ———–
1 Bill 11
2 Dhvani 20
3 Nehal 20
4 Abhinav 40
5 Nirmal 50
6 Sunil 95
7 Ram 100

(7 row(s) affected)

Sample - 3
SELECT ROW_NUMBER() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM Emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
2 Nehal 20 M
3 Sunil 95 M
1 Bill 11 S
2 Abhinav 40 S
3 Nirmal 50 S
4 Ram 100 S

(7 row(s) affected)

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s