question

kumar23 avatar image
kumar23 asked

'ROW_NUMBER' is not a recognized function name.

My query: **select row_number() over(partition by line_no order by line_NO) as orce from tx_hsa_summ** This is my query when ever i use the over function i'm keep on getting this error **Msg 195, Level 15, State 10, Line 1 'row_number' is not a recognized function name.** and this is my db version **Microsoft SQL Server 2000 - 8.00.2305 (Intel X86) May 14 2012 16:24:50 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)** pls help me resolve this.Thanks in advance
sql-server-2000row_number
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Just curious - why did you tag this as a SQL Server 2008 question? (I've retagged it to SQL Server 2000)
0 Likes 0 ·
eghetto avatar image
eghetto answered
Window functions (ROW_NUMBER) are not supported in SQL Server 2000.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kumar23 avatar image kumar23 commented ·
So how to overcome this pro?
0 Likes 0 ·
eghetto avatar image eghetto commented ·
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
There are ways of doing this in SQL2000, but we would need to know more about the table structure. Is there a column that uniquely identifies each row? Here's an example that uses a self join to get the same result as the `row_number()over(partition...)` code, but as you see it need a unique value on which to join declare @tx_hsa_summ table (uniqueid int, line_no int, blah varchar(10)) insert into @tx_hsa_summ select 1001,1, 'one' insert into @tx_hsa_summ select 1020,1, 'one' insert into @tx_hsa_summ select 1033,2, 'two' insert into @tx_hsa_summ select 1044,3, 'three' insert into @tx_hsa_summ select 1500,3, 'three' insert into @tx_hsa_summ select 1601,3, 'three' select uniqueid, row_number() over(partition by line_no order by line_NO) as orce from @tx_hsa_summ select t1.uniqueid, count(*) as seq from @tx_hsa_summ t1 join @tx_hsa_summ t2 on t1.line_no = t2.line_no and t1.uniqueid >= t2.uniqueid group by t1.uniqueid
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.