Return # of results that would have been returned without TOP

I want to return the first 100 results of a query that would otherwise retur
n
tens of thousands of results. To do this, I use SELECT TOP 100 ...
I'd really like to be able to show the user how many results would have been
returned had I not limited the query to 100 results. Is there a way to do
this in the same query?
The key is in the same query - obviously I could SELECT COUNT (*) and use
the exact same from and where clauses from the original query. That has two
big divantages. First, any time I need to change the query I need to
change two queries, and second, it's two trips to the database, and it
doesn't seem like there should be a need for that.
Thank you.Greg,
Try one of these approaches:
select top 10
OrderID,
(select count(*) from Northwind..Orders)
from Northwind..Orders
order by CustomerID
go
select top 10
OrderID, CustomerID, C.ct
from Northwind..Orders
cross join (
select count(*) as ct
from Northwind..Orders
) as C
order by CustomerID
Steve Kass
Drew University
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:E94CC761-3C77-43F0-BDA2-F646D609A837@.microsoft.com...
>I want to return the first 100 results of a query that would otherwise
>return
> tens of thousands of results. To do this, I use SELECT TOP 100 ...
> I'd really like to be able to show the user how many results would have
> been
> returned had I not limited the query to 100 results. Is there a way to do
> this in the same query?
> The key is in the same query - obviously I could SELECT COUNT (*) and use
> the exact same from and where clauses from the original query. That has
> two
> big divantages. First, any time I need to change the query I need to
> change two queries, and second, it's two trips to the database, and it
> doesn't seem like there should be a need for that.
> Thank you.|||Greg Smalter (GregSmalter@.discussions.microsoft.com) writes:
> I want to return the first 100 results of a query that would otherwise
> return tens of thousands of results. To do this, I use SELECT TOP 100
> ...
> I'd really like to be able to show the user how many results would have
> been returned had I not limited the query to 100 results. Is there a
> way to do this in the same query?
> The key is in the same query - obviously I could SELECT COUNT (*) and
> use the exact same from and where clauses from the original query. That
> has two big divantages. First, any time I need to change the query I
> need to change two queries, and second, it's two trips to the database,
> and it doesn't seem like there should be a need for that.
If you are on SQL 2005, you can do this:
WITH CTE (OrderID, CustomerID) AS
(SELECT OrderID, CustomerID
FROM Orders
WHERE OrderDate BETWEEN '19971201' AND '19971231')
SELECT TOP 10 OrderID, CustomerID,
Cnt = (SELECT COUNT(*) FROM CTE)
FROM CTE
This evades the maintenance problem entirely. Whether it is actually the
best from a performance point of view, requires benchmarking.
The alternative is to run the base SELECT into a temp table, and the
run a SELECT TOP and a SELECT COUNT from that one.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||on 2005, you can write this:
select top 10
OrderID,
row_number() over(order by CustomerId desc)
total_in_the_first_row
from Northwind..Orders
order by CustomerID
go
It's possible the performance will suck however|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> on 2005, you can write this:
> select top 10
> OrderID,
> row_number() over(order by CustomerId desc)
> total_in_the_first_row
> from Northwind..Orders
> order by CustomerID
> go
> It's possible the performance will suck however
And the output is somewhat incomprehensible:
OrderID total_in_the_first_row
11011 825
10952 826
10835 827
10702 828
10692 829
10643 830
10926 821
10759 822
10625 823
10308 824
The correct answer of 830 is in the middle...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thank you for the correction. Yet I guess something like this should
work if OrderID is a PK:
select top 10
OrderID,
row_number() over(order by CustomerId desc, OrderID desc)
total_in_the_first_row
from Northwind..Orders
order by CustomerID, OrderID
cannot test it until tomorrow however...