473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
Jul 20 '05 #1
5 16821
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
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
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
> 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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: jayson_13 | last post by:
Hi, I need to implement a counter and i face problem of locking so hope that u guys can help me. I try to do test like this : 1st connection SELECT * FROM nextkey WHERE tblname = 'PLCN'...
1
by: Andy | last post by:
Which is the better method to retrieve data from a database (SQL Server 2000 or Oracle 9i)? Does one of the following methods perform better than the other? 'METHOD 1 Dim strSql, strValue...
9
by: GD | last post by:
Hi, The following code: <script language="javascript" type="text/javascript"> function lstbox(lstval) { switch(lstval){ case "Amphipoda" : document.getElementById("clas").add(new...
6
by: Omar | last post by:
Hi, In a JSP I have the next: .... codigo = "<select name='" + nombre + "'>\n<option selected value='default'>Escoge</option><option value='todos'>Todos</option>"; if (miRS != null) while...
11
by: Michi Henning | last post by:
Hi, I'm using a blocking Select() call on a socket with a timeout value of -1. I'd expect the call to block indefinitely, but it doesn't. When I use Poll() instead, a timeout of -1 works fine...
1
by: VMI | last post by:
How can I run a Select query from a datatable so that it only brings the fields I want? For example, if my datatable has 30 fields, a query would be "select custId, Name" because those are the only...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
1
by: Maxwell2006 | last post by:
Hi, I am working with strongly typed datatables. What is the most efficient way to build a new DataTAble based on the result of DataTable.Select? At this point I use a foreach loop to do the...
6
by: Arthur | last post by:
Hello. How might it be possible to change where a form action is directed based on a selected option. For example I have this: <FORM METHOD = "post" ACTION = ""> And a drop down such as
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.