Row_number function is used to generate a serial number for a given record set. But you need to always use ORDER BY clause so that the numbers are assigned to the specific order.
Let us create the following dataset
CREATE TABLE #TEST (NAMES VARCHAR(100)) INSERT INTO #TEST SELECT 'PINAL' UNION ALL SELECT 'MAHESH' UNION ALL SELECT 'SUNIL' UNION ALL SELECT 'ARVIND' UNION ALL SELECT 'MURUGAN'
Suppose you want to generate row number, you can use the following statement
SELECT *,ROW_NUMBER() OVER (ORDER BY NAMES) AS SNO FROM #TEST
The reasult is
The numbers are assigned based on ascending order of name
But what if you want to generate row numbers in the same order the data are added.
Can you omit the ORDER BY Clause?
SELECT *,ROW_NUMBER() OVER () AS SNO FROM #TEST
The above throws the following error
Msg 4112, Level 15, State 1, Line 1
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.
But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below
SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST
The result is
In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
75 Comments. Leave new
wow .. nice.
Thanks Nitin.
Another way:
SELECT *,ROW_NUMBER() OVER (ORDER BY GETDATE()) AS SNO FROM #TEST
Good One Sanjay.
what is the benefit of the using 100 (or any number) in place of column name.
is it faster or any other?
ROW_NUMBER() expects a column to be ordered. If you do not want to order by any column, you can use any literal value
Hello Sir,
In sql server 2008 R2 what is the maximum number of columns and rows values passed in the form of xml parameter into stored procedure.
Thanks in advance,
Raja, Did you search on internet before posting here? I don’t know of any limitation.
Hi Pinal Sir,
Can you brief me about performance impact between order by Column OR order by Select 1.
Thanks
Asif
Asif, I would let you find that and reply.
Yes but order as entered is never garanteed without ORDER clause !
DELETE #TEST WHERE NAMES = ‘SUNIL’
INSERT #TEST VALUES (‘OTHER’)
SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST
— and the new line takes place of deleted line…
Best regards,
Arian.
Good catch Arian.
There are many methods to achieve this
Note – Flag is the name of the table
–METHOD 1 | Using SELECT NULL
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNumber FROM Flag
–METHOD 2 | Using %%physloc%%
SELECT * , ROW_NUMBER() OVER (ORDER BY %%physloc%%) RowNumber FROM Flag
–METHOD 3 | Using SELECT 1 (You can use any number here)
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber FROM Flag
–Method 4 | Using %%lockres%%
SELECT * , ROW_NUMBER() OVER (ORDER BY %%lockres%%) RowNumber FROM Flag
–Method 5 | Using sys.fn_PhysLocCracker(%%physloc%%) , file_id, page_id, slot_id
–Row Number Based on Physical Location in SQL Server
SELECT f.*,
ROW_NUMBER() OVER(ORDER BY file_id, page_id, slot_id) AS Row
FROM flag f
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
ORDER BY Row
MSBISkills.com
Hi Pinal,
Can you please elaborate ​the difference between SELECT 1, SELECT 0 and SELECT NULL?
There is no difference. It just needs a literal value. Thanks it. You can use any literal value
thanks, very helpful.
It’s worth mentioning that the same trick works for ‘OFFSET N ROWS FETCH NEXT X ROWS ONLY’ – which also requires explicit Order by. – just tested it on SQL2012
Much appreciated. Thank you.
@MattAkers – Thanks!
Thanks so much, much appreciated!
@hiladg – my pleasure. glad you liked it.
Clever trick. Btw I love your blog. I’ve been referencing it for years. Thanks for all of your hard work!
@Keith – Thanks for the comment.
it will work with order by (select ”) also
Good one! Thanks Sambasiva.
I need to sort a column on the basis of ROW_NUMBER() and order by ROW NUMBER itself. Is it possitbl ?
Or use:
ORDER BY (SELECT NULL);
You can in fact use any literal value
ORDER BY (SELECT ‘TEST’)
You can do ROW_NUMBER( ) OVER (ORDER BY (SELECT NULL) ) AS RowNum
…
ORDER BY RowNum
It hels.:)
It is to avoid sorting the rows with any columns.
I’ve been to this blog a lot of times over the years. Today you solved a tricky issue for me in such a simple way that I had to stop and say thanks. Thanks.
one of the best solution .. awesome it helped me with big problem