Count and top

I have complicated select statement which returns a lot of rows.
I would like to show only top 50 rows on client, but also notice the client
with the number of all rows of his search statement, like
Showing top 50 of 1000 rows.
How can I get count of all rows, something like:
SELECT top 50 *, count(all) from .......
One way is to create 2 select statements:
first one to get count of all records suitable to client search
SELECT count(*) FROM ....WHERE ...
and second to show the client only top 50 of them:
SELECT top 50 * FROM...WHERE ...
I wonder, can this be done with one select statement?
Regards,SSee the following link for a number of possible options.
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%232lQ41obGHA.3388@.TK2MSFTNGP05.phx.gbl...
>I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the
> client with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>|||You can use @.@.ROWCOUNT.
And make a sp returns ROWCOUNT.
SELECT Top 50 * FROM WHERE..,
SELECT @.@.ROWCOUNT
"simonZ"?? ??? ??:

> I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the clien
t
> with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>
>|||It can be done in one select that queries the table twice. Use a
subquery in the SELECT column list.
SELECT top 50 *,
(select count(all) from .......) as CountAll
FROM ......
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 10:42:07 +0200, "simonZ"
<simon.zupan@.studio-moderna.com> wrote:

>I have complicated select statement which returns a lot of rows.
>I would like to show only top 50 rows on client, but also notice the client
>with the number of all rows of his search statement, like
>Showing top 50 of 1000 rows.
>How can I get count of all rows, something like:
>SELECT top 50 *, count(all) from .......
>One way is to create 2 select statements:
>first one to get count of all records suitable to client search
>SELECT count(*) FROM ....WHERE ...
>and second to show the client only top 50 of them:
>SELECT top 50 * FROM...WHERE ...
>I wonder, can this be done with one select statement?
>Regards,S
>
>|||hongju je napisal:
> You can use @.@.ROWCOUNT.
> And make a sp returns ROWCOUNT.
> SELECT Top 50 * FROM WHERE..,
> SELECT @.@.ROWCOUNT
>
> "simonZ"=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1=ED=95=9C =EB=82=B4=EC=9A=A9:
>
ient