rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...