Hello Friends, till now you have learned regarding NTILE(), DENSE_RANK() and ROW_NUMBER(). Today we will learn about RANK in SQL. RANK() will returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of rank that come before the row in question.

Syntax : RANK ( ) OVER (PARTITION BY column_name ORDER BY column_name ASC/DESC)

“PARTITION BY” divides the result set into partitions to which RANK function is applied.

“ORDER BY” defines the order in which RANK function is applied in a partitions applied above if any.

Lets take an example for RANK in Sql Server

CREATE TABLE SALES
(
 ID INT IDENTITY(1,1) NOT NULL,
 FIRSTNAME VARCHAR(100) NOT NULL,
 LASTNAME VARCHAR(100) NOT NULL,
 PRODUCT VARCHAR(100) NOT NULL,
 PRICE DECIMAL(18,3) NOT NULL,
 COUNTRY VARCHAR(100) NOT NULL

)

INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('John','Elson','Samsung Galaxy Y',200.45,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Rehman','Malik','I Phone',800.65,'Pakistan')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Maulik','Jain','Samsung Galaxy Y',200.89,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Ching','Lee','HTC',700.87,'China')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Kerry','White','Sony Vaio 15212',900.22,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('John','Elson','I Phone',600.23,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('John','Smith','Samsung S3',700.45,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Thomas','Cook','HP Pavellion',1000.65,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Lax','Patel','Micromax Canvas',500.97,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Sanjay','Rana','Nokia 800',400.44,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Nirav','Shah','Creative 630',50.23,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Tang','Bao','Toshiba',800.10,'China')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Feng','Lee','HTC',450.41,'China')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Adil','Rehman','Creative 630',45.68,'Pakistan')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Ajmal','Hussain','Samsung Galaxy Y',200.89,'Pakistan')


SELECT 
RANK() OVER (  ORDER BY s.country) AS 'Rank',
s.country,s.FirstName, s.LastName
,s.product,s.price from SALES s

Have a look at the below screen shot, the rank is applied on below result with order by clause on country. The sales table have 15 records in which 3 for China,4 for India,3 for Pakistan and 5 for USA.  So “China” comes first in the list based on the order. As China have 3 records all the records has been allocated with “1”, the next record will start with 4(1+1+1 = 3 + 1(For next record)) as “1” is repeated three times.

You can also visit my live demo at  : Sql Fiddle

Don’t forget to share your thoughts. Thanks 🙂