October 15, 2009 at 7:28 am
Hi all
it is possible ,we can get a subset of rows according paging condition and total rows in one statement like
Select * From
(
Select ID,Name,row_number() over(order by ID) as Row from Table1
) as VT Where Row between 50 and 60
This query run but return only 10 records filter according paging condition.
but Without paging condition we get total 100 record.
we want get total record count in this statement.
is it possible
thanks in advance.
October 15, 2009 at 8:20 am
Hi
The trick is to use inline query. Notice that I moved your SQL to CTE to allow for complex, yet coordinated, where clause...
;WITH MySampleCTE AS
(
Select
row_number() over(order by name) as Row,
*
from
sys.objects
)
Select
*,
(SELECT COUNT(*) FROM MySampleCTE) as TotalRecords
From
MySampleCTE as VT
Where
Row between 50 and 60
October 20, 2009 at 12:08 am
Thanks For Reply
Your Solution is right. but according performance issue it is not good.
if we use 4 - 5 table in join and select a range from 2000 to 3000 row number then sub query use in this statement is execute every time.it mean 1000 times. so it is not good according performance.
please give me new ideas for solve this problems
October 20, 2009 at 1:38 am
You've posted into 'Sql Server 7,2000' forum are you not on a later release ?
In guessing so as you have used row_number().
See this article (and the following discussion for advice )
October 20, 2009 at 10:57 pm
Thank
This can help me
October 21, 2009 at 5:38 am
Great solution!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply