Hi All,
In different kind of situation we may required to generate rownumber on a table. Here I have explained three methods to get the rownumber on a table.
You can decide yourself to find the best approach based on your requirement.
Method: 1
USING RANK()FUNCTION:
Rank function generates the rownumber based on the Order by column and it considers same rank for same value in the given order by column and it will consider actual row number as next rank.
Ex:
——–USING RANK()———
SELECT RANK() OVER (
ORDER BY Title) AS [RANK],
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM [HumanResources].[Employee]
Method: 2
USING DENSE_RANK()FUNCTION:
DENSE_RANK () function generates the rownumber based on the Order by column and it considers same rank for same value in the given order by column and it will consider actual rank number as next rank.
Ex:
——–DENSE_RANK()———
SELECT DENSE_RANK() OVER (
ORDER BY Title) AS DENSE_RANK,
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM [HumanResources].[Employee]
Method: 3
USING ROW_NUMBER() FUNCTION:
ROW_NUMBER () function generates the rownumber based on the Order by column and it considers identity number for the given order by column.
EX:
——–ROW_NUMBER()———
SELECT ROW_NUMBER() OVER (
ORDER BY Title) AS RowNumber,
LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender
FROM [HumanResources].[Employee]
