SELECT Top. Which method is best?

Hi

SQL Server 7.0 using stored procedures.

I need to do a SELECT Top but I always need to find the record count
for the entire SELECT statement.

ID Name
-- ----
1 Abraham
20 Barrington
32 Callaway
54 Dennis
58 Emmett

If I do a SELECT TOP 3, I'll get the required records but get a
recordcount of 3.

I cant use the ROWCOUNT method either becuase the recordcount will
also be 3.

I can see two different options for getting back the information I
need:
1 - Run the select statement twice, first to retrieve the recordcount
and then with top to get the recordset

2 - Use a temp table to get the entire recordset and recordcount, and
then extract the Top N records

I'd like to know if anyone has any other suggestions. Currently, I'm
tempted to go for the second option....

Thanks

Sam

re: SELECT Top. Which method is best?

Try something like:

SELECT
*,
(SELECT
COUNT(*)
FROM MyTable
WHERE MyColumn = 'SomeValue') AS NumberOfRows
FROM MyTable
WHERE MyColumn = 'SomeValue'
ORDER BY ID


--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Samuel Hon" <noreply@samuelhon.co.uk> wrote in message
news:c8672b7d.0309190058.578bf0b7@posting.google.c om...[color=blue]
> Hi
>
> SQL Server 7.0 using stored procedures.
>
> I need to do a SELECT Top but I always need to find the record count
> for the entire SELECT statement.
>
> ID Name
> -- ----
> 1 Abraham
> 20 Barrington
> 32 Callaway
> 54 Dennis
> 58 Emmett
>
> If I do a SELECT TOP 3, I'll get the required records but get a
> recordcount of 3.
>
> I cant use the ROWCOUNT method either becuase the recordcount will
> also be 3.
>
> I can see two different options for getting back the information I
> need:
> 1 - Run the select statement twice, first to retrieve the recordcount
> and then with top to get the recordset
>
> 2 - Use a temp table to get the entire recordset and recordcount, and
> then extract the Top N records
>
> I'd like to know if anyone has any other suggestions. Currently, I'm
> tempted to go for the second option....
>
> Thanks
>
> Sam[/color]

re: SELECT Top. Which method is best?

Dan Guzman (danguzman@nospam-earthlink.net) writes:[color=blue]
> Try something like:
>
> SELECT
> *,
> (SELECT
> COUNT(*)
> FROM MyTable
> WHERE MyColumn = 'SomeValue') AS NumberOfRows
> FROM MyTable
> WHERE MyColumn = 'SomeValue'
> ORDER BY ID[/color]

I think you forgot a TOP:

SELECT
TOP 3 *,
(SELECT
COUNT(*)
FROM MyTable
WHERE MyColumn = 'SomeValue') AS NumberOfRows
FROM MyTable
WHERE MyColumn = 'SomeValue'
ORDER BY ID



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: SELECT Top. Which method is best?

Excellent! Thanks v much :D

For future reference, you need to add the rowcount

SET ROWCOUNT YourNumber[color=blue]
> SELECT
> *,
> (SELECT
> COUNT(*)
> FROM MyTable
> WHERE MyColumn = 'SomeValue') AS NumberOfRows
> FROM MyTable
> WHERE MyColumn = 'SomeValue'
> ORDER BY ID[/color]
SET ROWCOUNT 0

re: SELECT Top. Which method is best?

> I think you forgot a TOP:[color=blue]
>
> SELECT
> TOP 3 *,
> (SELECT
> COUNT(*)
> FROM MyTable
> WHERE MyColumn = 'SomeValue') AS NumberOfRows
> FROM MyTable
> WHERE MyColumn = 'SomeValue'
> ORDER BY ID[/color]

Yep. Thanks for adding it.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns93FBF0D8C249Yazorman@127.0.0.1...[color=blue]
> Dan Guzman (danguzman@nospam-earthlink.net) writes:[color=green]
> > Try something like:
> >
> > SELECT
> > *,
> > (SELECT
> > COUNT(*)
> > FROM MyTable
> > WHERE MyColumn = 'SomeValue') AS NumberOfRows
> > FROM MyTable
> > WHERE MyColumn = 'SomeValue'
> > ORDER BY ID[/color]
>
> I think you forgot a TOP:
>
> SELECT
> TOP 3 *,
> (SELECT
> COUNT(*)
> FROM MyTable
> WHERE MyColumn = 'SomeValue') AS NumberOfRows
> FROM MyTable
> WHERE MyColumn = 'SomeValue'
> ORDER BY ID
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

re: SELECT Top. Which method is best?

Sorry, didnt mention that the Top is a variable so cant use the SELECT TOP n

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message news:<orMab.39754$NM1.39620@newsread2.news.atl.ear thlink.net>...[color=blue][color=green]
> > I think you forgot a TOP:
> >
> > SELECT
> > TOP 3 *,
> > (SELECT
> > COUNT(*)
> > FROM MyTable
> > WHERE MyColumn = 'SomeValue') AS NumberOfRows
> > FROM MyTable
> > WHERE MyColumn = 'SomeValue'
> > ORDER BY ID[/color]
>
> Yep. Thanks for adding it.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Erland Sommarskog" <sommar@algonet.se> wrote in message
> news:Xns93FBF0D8C249Yazorman@127.0.0.1...[color=green]
> > Dan Guzman (danguzman@nospam-earthlink.net) writes:[color=darkred]
> > > Try something like:
> > >
> > > SELECT
> > > *,
> > > (SELECT
> > > COUNT(*)
> > > FROM MyTable
> > > WHERE MyColumn = 'SomeValue') AS NumberOfRows
> > > FROM MyTable
> > > WHERE MyColumn = 'SomeValue'
> > > ORDER BY ID[/color]
> >
> > I think you forgot a TOP:
> >
> > SELECT
> > TOP 3 *,
> > (SELECT
> > COUNT(*)
> > FROM MyTable
> > WHERE MyColumn = 'SomeValue') AS NumberOfRows
> > FROM MyTable
> > WHERE MyColumn = 'SomeValue'
> > ORDER BY ID
> >
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@algonet.se
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinf...2000/books.asp[/color][/color]