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 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
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
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
> 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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'...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |