What is Rank() in sql server.


If two or more rows tie for a rank, each tied rows receives the same rank. Rank() leaves a gap between the groups. But we need to use ORDER BY clause so that the numbers are assigned to the specific order.

CREATE TABLE #TEMP (NAMES VARCHAR(100))
INSERT INTO #TEMP
SELECT 'Hitesh' UNION ALL
SELECT 'Abhimanyu' UNION ALL
SELECT 'Priyanka' UNION ALL
SELECT 'Dev' UNION ALL
SELECT 'Dev' UNION ALL
SELECT 'Bhawesh'

SELECT NAMES,Rank() OVER (ORDER BY NAMES) AS SNO FROM #TEMP

drop table #TEMP

Result :

Rank

Rank