Generate Sequence Numbers in SQL Select Query or Include row number in query result in SQL Server

There are 3 types of functions which can be used to rank the records.

1. Row_number
2. Rank
3. Dense_rank

All these function makes use of OVER clause. The OVER clause determines the sequence in which rows are assigned a rank, it may be ascending or descending.

To properly view the functionality of these Ranking functions first we will create a table named StudentTotalMarks. To create this table use the below given query in Sql Server.

1. Row_Number
   This function will rank the column sequentially. Starting with 1 and then incrementing by 1 (eg.1,2,3,4), regardless of the same data in the column to be ranked.
   The following query displays the sequential number on a column by using Row_Number function.

       Select StudentName,MarksObtained,
       ROW_NUMBER() OVER(ORDER BY MarksObtained DESC) AS Rank
       FROM StudentTotalMarks
                   

   The following output shows the data retrieved from the query.
   

2. Rank

   This function will rank the column based on the specified criteria. If the table two student have same marks then this function will give them same rank, however the next rank number will be skipped (eg.1,2,3,3,5,6) .
   The following query displays the working of Rank function.

       SELECT StudentName, MarksObtained,
       RANK() Over(ORDER BY MarksObtained DESC) AS Rank
       FROM StudentTotalMarks
                   

   The following output shows the data retrieved from the query.
   
   Here three students got the same rank 2, so the next rank is 5 omitting 3 and 4 rank numbers.

3. Dense_Rank

   The Dense_rank() function is used where consecutive ranking values need to be given based on specific criteria. It Ranks the column in the same way as of Rank() function however rank is not skipped here (eg. 1,2,3,4,5,5,6)
   To view the functioning of the Dense_rank, write down the following query.
   SELECT StudentName, MarksObtained, DENSE_RANK() OVER(ORDER BY MarksObtained DESC) AS Rank FROM StudentTotalMarks
   The following output shows the data retrieved from the query.
   
   Here three students got same rank 2, so next rank is 3. here it is not 5 as it was in Rank() function.

You can also use those functions with Inner Join or Outer Join OR Cross Apply.

Below code uses Rank with Join

    SELECT StudentName, MarksObtained, c.CourseID, c.CourseName
    RANK() Over(ORDER BY MarksObtained DESC) AS Rank
    FROM StudentTotalMarks S
        JOIN CourseDetails C ON C.CourseID = S.CourseID 
        

Management Studio generates that on the fly. You can however, recreate a sequential number using the ROW_NUMBER ranking function if you are using SQL Server 2005 or later. Note you should never assume the database will return the rows in a specified order unless you include an Order By statement. So your query might look like:

Select ....
    , Row_Number() Over ( Order By T.SomeColumn ) As Num
From Table As T
Order By T.SomeColumn

The Order By in the Over clause is used to determine the order for creating the sequential numbers. The Order By clause at the end of the query is used to determine the order of the rows in the output (i.e. the order for the sequence number and the order of the rows can be different).

or

SELECT zip,city,state,latitude,longitude,timezone,dst,
          ROW_NUMBER() OVER (ORDER BY zip) AS num
          FROM dbo.zipcode;