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)
Related Articles
- Get the number of rows in all the tables of an SQL Server database.