Generate Row number in SQL using Row_Number() function.

ROW_NUMBER()  function is used to generate sequence number for each row in given result set as per partition. For each Partition it will start from 1.

Using PARTITION BY  Expression

In this way, we partition the result set produced by From clause to which Row_Number function is applied. We need to specify column(value_Expression ) by which we want to partition the result set. If Partition By not specified then Row_Number()  function will treat the whole result set as a single partition.

Using Order By Clause

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It will show logical order of the row.

Return Type: bigint

Using Order By Clause:

CREATE TABLE #Ranking (Grades VARCHAR(100))

INSERT INTO #Ranking(Grades) Values ('Grade1')

INSERT INTO #Ranking(Grades) Values ('Grade2')

INSERT INTO #Ranking(Grades) Values ('Grade3')

INSERT INTO #Ranking(Grades) Values ('Grade4')

INSERT INTO #Ranking(Grades) Values ('Grade5')

INSERT INTO #Ranking(Grades) Values ('Grade6')

INSERT INTO #Ranking(Grades) Values ('Grade7')

SELECT ROW_NUMBER() OVER(ORDER BY Grades DESC) AS Row,  Grades FROM #Ranking

Without using Column name in Order By Cause: ORDER BY a literal value

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO FROM #Ranking

Using PARTITION BY  Expression:

CREATE TABLE #Grades (Grades VARCHAR(100), Class VARCHAR(100), )

INSERT INTO #Grades(Grades,Class) Values ('Grade1','FirstClass')

INSERT INTO #Grades(Grades,Class) Values ('Grade2','FirstClass')

INSERT INTO #Grades(Grades,Class) Values ('Grade3','SecondClass')

INSERT INTO #Grades(Grades,Class) Values ('Grade4','SecondClass')

INSERT INTO #Grades(Grades,Class) Values ('Grade5','SecondClass')

INSERT INTO #Grades(Grades,Class) Values ('Grade6','ThirdClass')

INSERT INTO #Grades(Grades,Class) Values ('Grade7','ThirdClass')

SELECT ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Class DESC) as SNO,Grades,Class FROM #Grades