Ranking functions in sql

 CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

SELECT
       Salary
       , ROW_NUMBER() OVER(ORDER BY Salary ASC) AS 'ROWNUMBER'
       , RANK() OVER(ORDER BY Salary ASC) AS 'RANK'
       , DENSE_RANK() OVER(ORDER BY Salary ASC) AS 'DENSERANK'
       , ROW_NUMBER() OVER(PARTITION BY Salary ORDER BY Salary ASC) AS 'PARTITIONBY'
       , NTILE(3) OVER(ORDER BY Salary) AS 'NTILE'

 FROM #Employee

1. ROW_NUMBER() - Serial Number for each row.
2. RANK() - Rank each row and same record assign same rank and next rank number will be addition of the previous rank (same rank).
3. DENSE_RANK() - Rank each row and same record assign same rank and next rank number would be assigned the next rank value.
4. NTILE(3) - Groups the row based on number given.