Sql Server RANK

Rank function provider rank of rows of a particular set over a partition. The partition can be a full set of data or grouping of data .Rank always return bigint.

Syntax :

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

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,'34,student street','05/08/2018',12.5

UNION

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

UNION

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

UNION

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

UNION

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

UNION

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

UNION

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

Here is the example of RANK function with partition.

SELECT name,

age,

RANK() OVER (PARTITION BY age ORDER BY date_of_birth DESC) AS DRank

FROM student 

output  

name            age       DRank

John9    12    1
John5    12    2
John3    12    3
John1    12    4
John6    13    1
John4    13    2
John2    13    3
John8    14    1
John10   15    1
John7    15    2
John11   16    1

Here is rank over the age , separated by partition. 

Here is the example of RANK function without partition.

SELECT name,

age,

RANK() OVER (ORDER BY date_of_birth DESC) AS DRank

FROM student

output  

name            age       DRank

John11    16    1
John10    15    2
John9    12    3
John8    14    4
John7    15    5
John6    13    6
John5    12    7
John4    13    8
John3    12    9
John2    13    10
John1    12    11