Row Number Without Using Order By In SQL Server

You might have already known that using ROW_NUMBER() function is used to generate sequential row numbers for the result set returned from the select query. You have to use ORDER BY clause along with ROW_NUMBER() to generate row numbers. In this article, I will show you a simple trick to generate row number without using ORDER BY clause.

Normally you can use ROW_NUMBER() as in the example below along with ORDER BY.

SELECT 
	ROW_NUMBER() OVER(ORDER BY Employee_Name ASC) AS Row_Num, 
	Employee_Name 
	FROM MTB_Table_A
GO

/* Result */
Row_Num              Employee_Name
-------------------- ---------------
1                    Ashley
2                    Brayden
3                    Emily
4                    Emma
5                    Jacob
6                    Michael
7                    Olivia
8                    Ryan
9                    Tyler
10                   William

(10 row(s) affected)

If you try to use the ROW_NUMBER() function without ORDER BY clause, you will get an error as seen below.

SELECT 
	ROW_NUMBER() OVER() AS Row_Num, 
	Employee_Name 
	FROM MTB_Table_A
GO

/* Result */
Msg 4112, Level 15, State 1, Line 445
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

If you do not want to order the result set and still want to generate the row numbers, then you can use a dummy sub query column inside the ORDER BY clause. The dummy query will avoid ordering the result set in any sequence. In this example the dummy sub query I have used is Select 0. You can substitute 0 with any number or string value. With this you can generate row number without using ORDER BY clause.

SELECT 
	ROW_NUMBER() OVER(ORDER BY (Select 0)) AS Row_Num, 
	Employee_Name 
	FROM MTB_Table_A
GO

/* Result */
Row_Num              Employee_Name
-------------------- --------------------
1                    William
2                    Jacob
3                    Tyler
4                    Emma
5                    Ryan
6                    Ashley
7                    Brayden
8                    Olivia
9                    Michael
10                   Emily

(10 row(s) affected)

SQL Row Number Without Using Order By

Related Articles

Reference

Related Articles & Tools

Leave your thoughts...