Rank Given In Table

Rank Given in Table in SQL Server
If We Want to Give Indviual Rank And Same Rank to Same data Then Use

Use Of  ROW_NUMBER() Function 
1.This function will assign a unique id to each row returned from the query.
2.RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.


DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

Code
Create table-
CREATE TABLE Tbl_CityMaster1
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)
Data Insertd Then OutPut
OutPut-
Id  CityName   C_Id
1    Allahabad    1   
2    Allahabad   1
3    Allahabad    1   
4    Patna        1   
5    Patna        2   
6    Patna        3   
7    Varanasi    3   
8    Varanasi    3   
9    Varanasi    3   
10    Varanasi    3    

Give Rank 

Query-
SELECT
    Id,CityName,[C_id],
    Row_index = ROW_NUMBER() OVER(PARTITION BY [C_Id] ORDER BY [C_Id]),
    SameRank = DENSE_RANK() OVER (ORDER BY [C_Id])
FROM Tbl_CityMaster

OutPut

Id CityName C_Id RowNo SameRank
1    Allahabad    1    1      1
2    Allahabad    1    2      1
3    Allahabad    1    3      1
4    Patna           1    2      2
5    Patna           2    2      2
6    Patna           3     2      2
7    Varanasi      3     1      3
8    Varanasi      3     2      3
9    Varanasi      3     3      3
10    Varanasi    3    4       3