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.