Generate Row Number in SQL Server without using ROW_NUMBER( ) Function

Generate Row Number in SQL Server without using ROW_NUMBER( ) Function

Generating a row number for each row in SQL Server without ROW_NUMBER ( ) function is little bit tricky. The method which I am going to explain below is not even generic. this method only works if there is at least one unique column in the table (or collection of columns should be unique).

SELECT empid, empname, salary, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.empid >= i.empid) as Row_Num

FROM employee o order by Row_Num

The Column (empid) which we have used in above query for row number generation is called "sort key". For this technique to work , the sort key needs to be unique. I have chosen the column "empid" because this column contains unique value in our Employee table. If it was not Unique but some other collection of columns was, then we could have used those columns as our sort key  (by concatenating those columns to form a single sort key).

Also notice how rows are sorted in the result set. We have done an explicit sorting on the 'Row_Num' column, which gives us all the row numbers in the sorted order. But notice that 'empid' column is also sorted which is probably the reason why this column is referred as sort-key.

It's very easy to achieve above scenario using ROW_NUMBER ( ) inbuilt function as shown below.

SELECT empid, empname, salary, ROW_NUMBER() OVER (ORDER BY empid) as Row_Num FROM employee

How to generate Sequence without using Ranking Function in SQL Server ?

A
1
1
3
5
7
9
11
13
15
17
19
21

Expected output:

RN	A
1	1
2	1
3	3
4	5
5	7
6	9
7	11
8	13
9	15
10	17
11	19
12	21

SELECT COUNT(*) OVER(ORDER BY A, CONCAT(A,NEWID())) Row_Num, A FROM #temp