How to get Row Numbers from SQL Express 2005 query?

Hi,

I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error:

"The OVER SQL construct or statement is not supported."

So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)?

My current command is as follows:

SELECT (SELECT Row_Number()OVER (ORDER BY UserName)As RowNumber), *
FROM Users
ORDER BY UserName

Thanks for any advice you can offer!

like this

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

Hope this helps

|||

SELECT ROW_NUMBER() OVER (ORDER BY ProductID) as RowNumber,ProductID, ProductName, UnitPrice FROM Products WHERE RowNumber BETWEEN 20 AND 30;

|||

Thank you both for your replies. I'm sorry, I think I my question may have been a bit misleading. I know that you can return a limited result set by using the statements you have provided, but the error message I am getting is as follows:

"The OVER SQL construct or statement is not supported."

Both your suggestions use the OVER construct, and this does not appear to be supported bySQL Server Express 2005. If I run your suggested queries inVisual Studio 2005 I get the aforementioned error and the query will not run (though the SQL validation check says it's fine).

So, is the "Row_Number() OVER" command supported by SQL Express 2005 or not? If not, what other ways can I return a limited result set?

|||

i think you are using the designer, go in the sql server management studio and to this

Select File / New / Query and type your queries in the editing window instead.

Hope this helps

|||

Run this in SQL SERVER 2005 Management Studio Express:

EXEC sp_dbcmptlevel yourDataBase

If the current compatibility level of your database is 80, then run this:

EXEC sp_dbcmptlevel yourDataBase, 90

You need the compatibility level at 90 to run the Row_Number() OVER() query and other new features.

|||

Hi Limno,

That sounds promising. I've tried getting my website database into SQL Server Management Studio Express in the past but without success. My database is held as a .MDF file within my project - is there a way of importing this directly into Management Studio? The "Connect To Server" dialog displayed on startup does not allow me to browse to a specific .MDF file, and the File -> Open dialog has no option to open .MDF files.

Thanks

|||

I just found out how to import an MDF here:

http://forums.asp.net/p/1147899/1871779.aspx#1871779

And my database shows a compatibility rating of 90. Using the Row_Number() command works within SQL Server Management Express Studio! In that case, how can I perform a query in Visual Studio 2005 that uses this command if Visual Studio does not support this command?

I'm using strongly typed Table Adapters and Data Tables using the DAL component in Visual Studio. Is it impossible? If not, can I at least perform this query programmatically (C#) and cast the results to my strongly typed Data Table? If it can be done programmatically, does anyone have any examples of how to do this?

Thanks again!

|||

In Visual Studio 2005, you will see that not support message. Have you tried to ingnore it and see what will happen? It seems is should work fine. Another way, you can wrap your query in a Stored Procedure to work with.