ROW_NUMBER()

Hi,

I use SQL 2005 and Visual Studio 2005.

I tried to use the ROW_NUMBER() function but I always get an error message saying ( The Over SQL construct or statement is not supported.

SELECT row_number() over (order by fullname) as ROWNUMBER, CustomerID, FullName, Address, PhoneH, PhoneMob, Area, DayNumber
FROM Customers

Thanks.

Try this sample:

Code Snippet

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY FirstName ),
EmployeeID,
FirstName,
LastName
FROM Employees

RowNumber EmployeeID FirstName LastName
-- -- - --
1 2 Andrew Fuller
2 9 Anne Dodsworth
3 3 Janet Leverling
4 8 Laura Callahan
5 4 Margaret Peacock
6 6 Michael Suyama
7 1 Nancy Davolio
8 7 Robert King
9 5 Steven Buchanan

If you do NOT get the same output, please verify your SQL Server version (using @.@.Version).

|||

Hi Mohamed,

Be sure that you are connecting to a 2005 instance and check that the compatibility of the database you are connecting to is 90. See sp_dbcmptlevel in BOL for more info.

AMB

|||

You probably connected the SQL Server 2000 from the Management Studio. Over clause is only accepted by SQL Server 2005.

Execute the following query..

Select @.@.VERSION

It should return as Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) .....etc.

Note:

Database Compellability level won’t affect the OVER clause. So you can utilize the OVER clause in any of the Compellability Level (60, 65, 70, 80, or 90), but it should be SQL Server 2005 or above.

|||

Manivannan.D.Sekaran wrote:

You probably connected the SQL Server 2000 from the Management Studio. Over clause is only accepted by SQL Server 2005.

Execute the following query..

Select @.@.VERSION

It should return as Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) .....etc.

Note:

Database Compellability level won’t affect the OVER clause. So you can utilize the OVER clause in any of the Compellability Level (60, 65, 70, 80, or 90), but it should be SQL Server 2005 or above.

Thanks for your kind reply,

I used (select @.@.version) and got this result.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I still have the same error message.

Thanks.

|||

What environment/tool are you using when the error message occurs?

Are you in a query window in SQL Server Management Studio (File->New->Query with current connection)?

Are you using Query Designer?

Are you using something in Visual Studio?

If you're not issuing the command from a query window in SQL Server Management Studio, then please try that.

|||

Yes. OVER Clause is not working with Query Designer (on Management Studio or Visual Studio).

But it is not a error. You can ignore and continue your rest of work.... On the execution time you will get the proper result

|||Yet another reason to dislike those parts of the tools. I would suggest (if you are a professional programmer, which is likely if you are in these forums that you start writing queries only in the text editor. It will greatly improve your querying skills (and you can use all of the power of SQL Server without crazy tool errors!|||

Manivannan.D.Sekaran wrote:

Yes. OVER Clause is not working with Query Designer (on Management Studio or Visual Studio).

But it is not a error. You can ignore and continue your rest of work.... On the execution time you will get the proper result

Cheers Manivannan,

Thank you very much for your great help. It worked as magic. Without your help, I would have suffered a lot.

I want also to thank all friends who replied my question. I got benefit from each and all replies.

Thanks to

Arnie Rowland

hunchback

Dalej

and Louis Davidson