ROW_NUMBER() paging - CTE or subquery ?

For paging in SQL 2005, ROW_NUMBER() is recommended. But is it best practice to use that with a CTE or a Subquery ? I get the same query plan for the two examples below. One link I found suggests CTEs are preferred:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx

Thoughts on this ?

Thanks,

Andy Mackie

Code Snippet

USE AdventureWorks;

GO

--Using a CTE

WITH OrderedOrders AS

(

SELECT SalesOrderID, OrderDate,

ROW_NUMBER()OVER(ORDERBY OrderDate)AS'RowNumber'

FROM Sales.SalesOrderHeader

)

SELECT*

FROM OrderedOrders

WHERE RowNumber BETWEEN 50 AND 60;

--Using a subquery

SELECT*

FROM

(

SELECT SalesOrderID, OrderDate,

ROW_NUMBER()OVER(ORDERBY OrderDate)AS'RowNumber'

FROM Sales.SalesOrderHeader

) OrderedOrders

WHERE RowNumber BETWEEN 50 AND 60;

I think that no difference here. Because SQL Optimizer is very smart and it will generate same execution plan.sql

row_number() Help!

I have a

searching SP that uses the row_number() function to help out with

paging. But for some weird reason Im getting duplicate results when I

run the query. But if I take out the row_number() funciton I get normal

results. Here is what I have got, please help me with getting this

solved, im tearing my hair out!

WITH SearchTable AS
(
select distinct
st.id,
st.title,
st.sub_title,
st.synopsis,
st.short_code,
ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber

from stock as st left join
Keywords on Keywords.stock_id = st.id left join
OnlinePreviews opLow ON opLow.stock_id = st.id and opLow.[type] = 1 left join
OnlinePreviews opHigh ON opHigh.stock_id = st.id and opHigh.[type] = 2 left join
TeachersNotes tn ON tn.stock_id = st.id inner join
Stock_Subjects ss ON ss.stock_id = st.id inner join
Subjects sub ON sub.id = ss.subject_id
)

select
id,
title,
sub_title,
synopsis,
short_code,
RowNumber
from SearchTable st
Where
RowNumber between ((@.page - 1) * @.results) AND (@.page * @.results)
Order by short_code

So

thats the SQL, but keep in mind the entire SQL works fine if we take

all all references to the row_number() function. If I leave the

row_function() in then I can work out that it is my inner joins thats

the problem, if I slowly remove the inner joins and keep in the

row_number(), I figured out that the joins on the Stock_Subjects table

is the problem.

Why would this give different results when I use the row_number() funciton?

Thanks heaps to who ever solves this, I just can't figure it out!You will have to post a repro script that demonstrates the problem. ROW_NUMBER will generate unique numbers per row even if there are duplicates.|||As it turns out you are on the right track. I was selecting distinct so as try to eliminate the duplicate rows made from some joins in my query. But I also really needed to select the row_number() which, as you mentioned, was giving back a unique row number so distinct was effectivly useless.

ROW_NUMBER() function is not recognized in store procedure.

Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change.

CREATE PROCEDURE GetProductsOnCatalogPromotion
(@.DescriptionLength INT,
@.PageNumber INT,
@.ProductsPerPage INT,
@.HowManyProducts INT OUTPUT)
AS
-- declare a new TABLE variable
DECLARE @.Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion bit,
OnCatalogPromotion bit)
-- populate the table variable with the complete list of products
INSERT INTO @.Products
SELECTROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @.DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1
-- return the total number of products using an OUTPUT variable
SELECT @.HowManyProducts = COUNT(ProductID) FROM @.Products
-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @.Products
WHERERowNumber > (@.PageNumber - 1) * @.ProductsPerPage
ANDRowNumber <= @.PageNumber * @.ProductsPerPage

What version of SQL Server is this running on? ROW_NUMBER() was introduced in 2005, so any version before that won't have it.

Don

|||

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

I want to fetch records for paging purpose by its row numbers. so i tried this store proc. is there any other way to fetch without row numbers?

please reply.

|||

prasad bhanage:

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

Row_number() is a T-SQL enhancement that has been introduced in SQL 2005 only. If your database is in SQL 2000 you can't use any of the features introduced after SQL 2000 even if you run them using SQL 2005 tools. visithttp://msdn2.microsoft.com/en-us/library/ms186734.aspx for more information.

Now, to solve you problem as you've SQL 2000, there is one way of using temporary table for this. Your modified procedure is as below. Please make the logical changes as you wish.

CREATE PROCEDURE GetProductsOnCatalogPromotion(@.DescriptionLengthINT,@.PageNumberINT,@.ProductsPerPageINT,@.HowManyProductsINT OUTPUT)ASSELECT identity (bigint , 1 , 1 )as RowNumber , ProductID ,Name ,SUBSTRING(Description , 1 , @.DescriptionLength ) +'...'AS Description , Price , Image1FileName , Image2FileName , OnDepartmentPromotion , OnCatalogPromotioninto #ProductsFROM ProductWHERE OnCatalogPromotion = 1order by Product.ProductIDSELECT @.HowManyProducts =COUNT ( ProductID )FROM #ProductsSELECT ProductID ,Name ,Description , Price , Image1FileName ,Image2FileName , OnDepartmentPromotion , OnCatalogPromotionFROM #ProductsWHERE RowNumber > ( @.PageNumber - 1 ) * @.ProductsPerPageAND RowNumber <= @.PageNumber * @.ProductsPerPage

Hope this will help.

|||

Hi,

You need to create the database in sqlserver 2005.

Then only it will work other wise u can't use perticular keyword in Sql Server 2000.

It was newly introduced in sqlserver 2005 Only.

_____________________________________________________________

Mark as Answer If u find a solution.

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much

Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.sql

Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.

ROW_NUMBER() and projected row count.

Is there a way without rerunning the select query to get the "total row count" using

ROW_NUMBER and BETWEEN as such..

SELECT * FROM

(SELECT ROW_NUMBER() OVER(ORDER BY Year DESC, Month DESC, Day DESC) as RowNum,

e.id, e.Title

FROM Events e

) as DerivedTableName

WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1

typically i would build a temp table and return SELECT @.@.ROWCOUNT

? Why not just do: SELECT COUNT(*) FROM Events e -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <jkgreer@.discussions.microsoft.com> wrote in message news:f54208af-7f15-440a-b9fa-41e0f8c59ada@.discussions.microsoft.com... Is there a way without rerunning the select query to get the "total row count" using ROW_NUMBER and BETWEEN as such.. SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Year DESC, Month DESC, Day DESC) as RowNum, e.id, e.Title FROM Events e ) as DerivedTableName WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1 typically i would build a temp table and return SELECT @.@.ROWCOUNT|||

yeah that is pretty much what i'm left with right now but, that was an overly simplified example.

i'm feeding very complex where and order statements into it and if there isn't any overhead in rerunning the query i wouldn't mind.

knowing the virtual count of rows / pages seems to go hand in hand with paging and i wanted to make sure that switching to using ROW_NUMBER() and BETWEEN was a better choice than creating a temp table of ordered indexes and joining off of it just to retrieve the full count of filtered rows.

|||? Yes, I believe that the ROW_NUMBER solution is far superior to the temp table. I also think that running the query twice is not a huge issue if your users page past the first page on most searches. In that case, it means a better user experience. But if that's not the case, I might think twice about taking the full count if I were you. When it comes to paging, it's really a choice of performance vs. a slightly less functional UI -- and in many cases the full count really doesn't add that much anyway. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <jkgreer@.discussions.microsoft.com> wrote in message news:ea3176ea-5d77-4399-a575-31284186b42e_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com yeah that is pretty much what i'm left with right now but, that was an overly simplified example. i'm feeding very complex where and order statements into it and if there isn't any overhead in rerunning the query i wouldn't mind. knowing the virtual count of rows / pages seems to go hand in hand with paging and i wanted to make sure that switching to using ROW_NUMBER() and BETWEEN was a better choice than creating a temp table of ordered indexes and joining off of it just to retrieve the full count of filtered rows.|||

Thanks. that is what i expected i suppose.

I was going for the full featured with this question, but i have no issue letting users fall off the end of a page in places, point well taken.

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

Row_number selecting from a complex select statement

Hi,

Code Snippet

This is difficult to explain in words, but the following code outlines what I am trying to do:

with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum

Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

The following query might help you,

Code Snippet

;with UnionResult(myvalue,insertdate)

as

(

select table1Id As myValue,insertdate from myTable1

union

select table2Id As myValue,insertdate from myTable2

),

OrderedResult(myValue,Row)

as

(

select myValue, row_number() over (order by insertdate desc)

from UnionResult

)

select * from OrderedResult

|||

I m not sure I understand your requirment correctly,anyhow your query throws error,

Try the following

Code Snippet

;with myTableWithRowNum as

(

select 'row' = row_number() over (order by insertdate desc), myValue

from

(

select insertdate,table1Id As myValue from myTable1

union

select insertdate,table2Id As myValue from myTable2

) as temp

)

select * from myTableWithRowNum

|||Thanks that's exactly what I'm looking for.
sql

row_number query

If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegrou
ps.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

row_number query

shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegroup
s.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?|||shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegroup
s.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

row_number query

If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to the same value for
every row. This means that SQL Server can access the data in any way it finds most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a column which is unique.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

ROW_NUMBER is very slow on large tables

Hi all,
I thought that ROW_NUMBER is designed to handle paging on large tables.
However, when I test it on a table with 1,000,000 records, a simple select
with ROW_NUMBER hangs for more than 10 minutes.
Any idea what can be done?
My table is:
Events (ID, Date, Desc)
My query is:
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100You still need to have an index on the column ID. Also maybe you have
other issues such as blocking and the problem is not with the
row_number function.
Adi|||I think your query is wrong.
try this and let me know if it works
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events order by id asc
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100|||Thank you, but this query is just as slow as mine.
"Omnibuzz" wrote:

> I think your query is wrong.
> try this and let me know if it works
> SELECT * FROM
> (
> SELECT TOP 100 *, ROW_NUMBER()
> OVER (ORDER BY ID ASC) as RowNo
> FROM Events order by id asc
> ) as SortedEvents
> WHERE RowNo > 50 and RowNo < 100|||then can you tell me if this query is fast? do you have an index on ID?
SELECT * FROM
(
SELECT TOP 100 *
FROM Events order by id asc
) as SortedEvents|||Thank you.
ID has index.
Could you please elaborate on what is the blocking issue?
I'm testing on a standalone, development server.
Nobody else uses it.
"Adi" wrote:

> You still need to have an index on the column ID. Also maybe you have
> other issues such as blocking and the problem is not with the
> row_number function.
> Adi
>|||Anton,
try this:
SELECT Events .* FROM Events join
(
SELECT TOP 100 id, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
on events.id = SortedEvents.id
WHERE RowNo > 50 and RowNo < 100|||The nesting and the proprietary TOP 100 might be causing problems.
ROW_NUMBER () is new and probalby not well-optimized yet. Keep it
simple
SELECT event_id,
ROW_NUMBER()
OVER (ORDER BY event_id ASC) AS rn
FROM Events
WHERE rn BETWEEN 50 AND 100;|||Nonsense.Several years ago all db vendors acknowledged that all major
db problems had been solved (so they were free to add xml to the engine).
This is just another example of a user underming the operation of the db
by doing something silly and not informing the ng of exactly what it is.
'It's the user stupid' hangs on the wall of all vendors (and in the minds of
most responders:).
On a side note I still do not see any explanation from you from the mind
'set'
to windowing.So yesterdays criminial magically becomes todays most decorated
cop.
Code,code and nothing but code is STILL non-sense.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365856.484616.283960@.j33g2000cwa.googlegroups.com...
>.
> ROW_NUMBER () is new and probalby not well-optimized yet.
>.|||Steve Dassin wrote:
> Nonsense.Several years ago all db vendors acknowledged that all major
> db problems had been solved
Really? Please amuse me by posting an example of some vendor making
such a claim. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

'ROW_NUMBER' is not a recognized function name.

I am getting the following error while excuting following query in sqlserver 2005:

SELECT ProductName, UnitPrice,

ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank

FROM Products

ORDER BY UnitPrice DESC

if any one know what should be done to avoid this, please let me know.

Thanks in advance,

Rajanikanth.

Check and see if the database is running in SQL Server 2000 compatibility mode. Try running these two commands:

Code Snippet

select @.@.version

exec sp_dbcmptlevel 'yourDatabaseName'

|||

As Kent stated, check that you are connecting to a SS 2005 server. You could be using the client tools shipped with 2005, but if you connect to a 2000 instance, for example, then you will not be able to use the new features from 2005. The db compatibility level does not limit you from using the new features, if it is hosted in a 2005 instance.

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

sql

'ROW_NUMBER' is not a recognized function name.

Hi experts,
recently i download the SQLExpress 2005 version to do some testing,
when i try to run a new feature which all ROW_NUMBER, but SQL give me
error
'ROW_NUMBER' is not a recognized function name.
i wondering after install the 05, we need to add-on some package to
support this kind of feature,. which is not default '
* the sample is from
http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
pls give advise, many thanksJust the routine check. I hope you are not connecting to a SQL Server 2000
instance from the SQL Srvr Management Studio express edition.|||Check your database compatibility level
exec sp_dbcmptlevel '<Name of db>'
HTH
Kalen Delaney, SQL Server MVP
"XJ" <ianyian@.hotmail.com> wrote in message
news:1148698630.294392.99660@.g10g2000cwb.googlegroups.com...
> Hi experts,
> recently i download the SQLExpress 2005 version to do some testing,
> when i try to run a new feature which all ROW_NUMBER, but SQL give me
> error
> 'ROW_NUMBER' is not a recognized function name.
> i wondering after install the 05, we need to add-on some package to
> support this kind of feature,. which is not default '
> * the sample is from
> http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
> pls give advise, many thanks
>|||Hi Kalen,
i catch u, which give me 80, yes i agree that i have sql 2000
before, but i already uninstall it, rght now under the service name
call XJ\SQLEXPRESS.
and i have use "Microsoft SQL Server Management Studio Express",
but even i try to create a new DB, still give me 80,. pls give some
idea how can i create a DB which have 2005 feature under "studio
express" or any other way ,. .. many thanks|||Hi Kalen , Omnibuzz
Bcos the instance make me some confuse ,. finally i can
connect ! ya ,. many thanks !!!!

Row_Number function in WHERE clause

What is the reason that you cannot use the results of the ROW_NUMBER function in a WHERE clause? I can achieve the results that I want by using a derived table, but I was just looking for the exact reason.

I have my speculations that it is because the results of ROW_NUMBER are applied after the rows are selected and filtered, but I'd like something definitive.

Thanks a bunch in advance.

That would be the exact reason. The ROW_NUMBER function numbers output rows, so if you used it in the WHERE it would have to ignore rows that would not meet the criteria to be output.

Otherwise if it applied at the FROM level, there might be gaps in the sequence.

|||Louis,

Thank you very much for the answer.

Row_Number Function

i want distinct rows from the table
i want to use "Row_Number" function with this
so
how can i use "Row_Number" function when distinct keyword is used
so i get the distinct rows as well as Row Numbers

suppose my table is as below:

Row Nos id class name

11mca sasfdfj
21mca jklj
32mca jljlj
42mca jkljljl
53mcs gghgh
64mca gghgh
73mcs gghghUsing distinct keyword is equivalent to using group by specifying all fields, so you can just use group by, and specify ROW_NUMBER as usual

ROW_NUMBER and LEFT JOIN

Hi,

I don't have a problem, but I have run into an SQL 2005 "behavior" that I can't explain. So, I thought I'd ask here.

My question is how come that the below script will return 1 row if DISTINCT keyword is present and 3 rows if it's not? I would have guessed that it should return 3 rows for both versions of the batch.

Declare @.Movies TABLE(ID int,Namenvarchar(100))

Declare @.Comments TABLE(ID int, MovieID int, Comment nvarchar(100))

INSERTINTO @.Movies VALUES(1,'Pulp Fiction')

INSERTINTO @.Comments VALUES(1, 1,'Good movie.')

INSERTINTO @.Comments VALUES(2, 1,'Sucked!')

INSERTINTO @.Comments VALUES(3, 1,'Terrific.')

SELECT

DISTINCT

ROW_NUMBER()OVER(ORDERBY M.ID DESC)AS RowNum,

M.ID

FROM

@.Movies AS M

LEFTJOIN

@.Comments AS C

ON

C.MovieID = M.ID

GO

In the Execution Plan I can see that, if the keyword DISTINCT is present, SQL won't "touch" the Comments table, and if DISTINCT is present it will deal with the Comments table, so that gives me a clue, but I'd still like to hear an offical explanation if there is one.

Thanks.

Since you haven't included any columns from the @.Comments table, the "distinct" will cause the same number of rows to be returned with or without the left join to that table, so the query does not require access to @.Comments.

Ron Rice

row number

update [Costing].[dbo].[Supplier]

set n =

(

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

where t.suppliercode = Supplier.suppliercode

)

hi i cannot update a column with the row number,

it is all taking 1001, supposed to be 1001,1002 and so on .

thanks.

Hi,

Use better something like this here:

update [Costing].[dbo].[Supplier]

set n = Subquery.RowNumber

FROM [Costing].[dbo].[Supplier] S

INNER JOIN (

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

) Subquery

ON Subquery.suppliercode = S.suppliercode

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks it works , i did it shortcut way , not a good solution though.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER procedure [dbo].[DebugCursor]

as

BEGIN

SET NOCOUNT ON;

DECLARE cursorDebug Cursor FOR Select suppliercode

From supplier

Declare @.a nvarchar(25)

declare @.c int

set @.c = 300

Open cursorDebug Fetch NEXT FROM cursorDebug INTO @.a

While @.@.FETCH_STATUS =0

BEGIN

Update supplier Set n = @.c

Where suppliercode = @.a

set @.c = @.c + 1

Fetch NEXT FROM cursorDebug INTO @.a

END

CLOSE cursorDebug

DEALLOCATE cursorDebug

END

|||YOu should always prefer set based operations.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.desql