By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,679 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

SELECT Top. Which method is best?

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@posting.google.c om...
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

Jul 20 '05 #2

P: n/a
Dan Guzman (da*******@nospam-earthlink.net) writes:
Try something like:

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


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
Excellent! Thanks v much :D

For future reference, you need to add the rowcount

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

SET ROWCOUNT 0
Jul 20 '05 #4

P: n/a
> 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
Yep. Thanks for adding it.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1... Dan Guzman (da*******@nospam-earthlink.net) writes:
Try something like:

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


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, so****@algonet.se

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

Jul 20 '05 #5

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

"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message news:<or*******************@newsread2.news.atl.ear thlink.net>...
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


Yep. Thanks for adding it.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...
Dan Guzman (da*******@nospam-earthlink.net) writes:
Try something like:

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


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, so****@algonet.se

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

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.