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

 

SQL Server DISTINCT

DISTINCT return only distinct value. If any column content similar data and DISTINCT is applied on it,  the column value may or may not be repetitive, they comes only once in the output. Duplicate value is discarded , if it is already in the list. 

District can be applied almost type of data. District can be applied on various numeric functions like average,sum,count etc.

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  DISTINCT  is applied on numeric values

Example 1

SELECT DISTINCT age FROM student

Output

age

12
13
14
15
16

Here  DISTINCT  is applied on string values

Example 2

SELECT DISTINCT adress FROM student

Output

adress

34,student street
35,student street
36,student street
37,student street

Here  DISTINCT  is applied on Average values

Example 3

SELECT AVG(DISTINCT age) as Avarage FROM student

Output

Avarage 

14

Here  DISTINCT  is applied on Count values

Example 4 

SELECT COUNT(DISTINCT adress) as Adress_Count FROM student

Output

Adress_Count  

4