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
Hi sit i want to partion my row_number for exampl i hava 2000 row how can bring 1 to 20 and 20 to 40 and so
what is the use of Row Number partition by ?
It will reset the numbering for each group
it helped me with big problem
That;s great @Suraj
Muchasa Gracias
Con gusto
Excellent! Just what I was looking for. Thank you!
Welcome @Manfred
Is it true that the order cannot be guaranteed when using this approach? (i.e. Does that mean if we try to grab the first or last 100 rows – we will never be guaranteed they will be the same rows each time?)
True.
Brilliant.. saved my day
Thanks gamodg.
You rocks! Thanks
Thanks Andres
How could I number rows two by two? For example: have column “A” read 1,1,2,2,3,3,4,4 etc
Excellent! finally got the solution.. thnx alot!!
How do you got to this soluction? What is the “principle” behind this? (By the way, this help me a lot!!)
Hi Pinal,
I wanted to use a loop through an actual table not temp table using a ROW_NUMBER(). Is this possible?
I had a WITH() for the query and then want to traverse, but I think we cannot use a WHILE after the WITH. It has to be a query.
Can you please help me?
Uday
What do you want to do by using LOOP? If you can provide some sample data with expected result, it is easy to answer. There may be simpler method than what you are thinking
Hi Pinal,
I want to loop through an actual table, not temp table using ROW_NUMBER(). Is it possible?
WITH tempwkOrdersData AS
(
SELECT ROW_NUMBER() OVER(ORDER BY WORPrimaryId ASC) AS RowNo,
RowGuId, WORKORDER, WORPrimaryId , WCCPrimaryId , WKRPrimaryId,
FROM tempwkOrders
)
Now I want to have a WHILE loop to iterate into tempwkOrdersData using, RowNo = 1, RowNo = 2 and so on.
Please help me regarding this.
Thank you,
Uday
Perfect! tks
Nice
I now used
alter table #test add RowNr int identity(1,1)
But this is better
And by adding names you can also sort on names as well
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100),Names) AS SNO,* FROM #TEST
Could you explain why this works?
It works because in ORDER BY, you can use any literal value
I have bookmarked this link. Thank you sharing the method!
Hi guys,
I have used this post in some job assignments and it was a great help but I have found a “glitch” when filtering the results and I want to share it. I managed to overcome the issue but I think someone might find it interesting:
When I issue the SQL query and filter by a column= CONSTANT it will bring me certain order different when filtering the same query with column = @VARIABLE.
I just made this example.
select
row_number() over (ORDER BY (SELECT NULL) ),
* from master..sysobjects with (nolock) where xtype’U’
ORDER BY 1
declare @variable char
set @variable=’U’
select
row_number() over (ORDER BY (SELECT NULL) ),
* from master..sysobjects with (nolock) where xtype=@variable
ORDER BY 1
Given Row number according to record insert in table
but in table not any date for insert and not any identity column it’s possible ?
Please give response for that.
Given Row number according to record inserting time in table
but in table not any date for insert and not any identity column, it’s possible ?
Please give response for that.
Yes possible. Just follow what is suggested in the post
hi i am using union all with row number but cant get the with where query, here is the query below –
FROM (SELECT *,
Row_number() OVER(ORDER BY qtype) as row_num
FROM (SELECT id as id,
Pages.page_title as title
,’pages’ as qtype
FROM Pages
UNION ALL
SELECT id as id,
product_name as title
,’product’ as qtype
FROM products) a) b
WHERE row_num between 1 and 10
the problem is the unioned table row number is not in sequence even after trying your method (select 100) it return the position of row result of unioned table any help is appreciated,
thanks
It would be helpful if you post some sample data with expected result
Thanks for the trick to make this work!