CODEWITHSUNDEEP
sql-servert-sqlselect
sOltan
sOltan

Reputation: 493

T-SQL get row count before TOP is applied

I have a SELECT that can return hundreds of rows from a table (table can be ~50000 rows). My app is interested in knowing the number of rows returned, it means something important to me, but it actually uses only the top 5 of those hundreds of rows. What I want to do is limit the SELECT query to return only 5 rows, but also tell my app how many it would have returned (the hundreds). This is the original query:

SELECT id, a, b, c FROM table WHERE a < 2

Here is what I came up with - a CTE - but I don't feel comfortable with the total row count appearing in every column. Ideally I would want a result set of the TOP 5 and a returned parameter for the total row count.

WITH Everything AS
(
   SELECT id, a, b, c FROM table
),
DetermineCount AS
(
   SELECT COUNT(*) AS Total FROM Everything 
)
SELECT TOP (5) id, a, b, c, Total
FROM Everything 
CROSS JOIN DetermineCount;

Can you think of a better way?

Is there a way in T-SQl to return the affected row count of a select top query before the top was applied? @@rowcount would return 5 but I wonder if there is a @@rowcountbeforetop sort of thing.

Thanks in advance for your help.

** Update **

This is what I'm doing now and I kind of like it over the CTE although CTEs as so elegant.

-- @count is passed in as an out param to the stored procedure

CREATE TABLE dbo.#everything (id int, a int, b int, c int);

INSERT INTO #everything 
SELECT id, a, b, c FROM table WHERE a < 2;      

SET @count = @@rowcount;
SELECT TOP (5) id FROM #everything;
DROP TABLE #everything;

Upvotes: 4

Views: 7590

Answers (3)

sOltan
sOltan

Reputation: 493

** Update **

This is what I'm doing now and I kind of like it over the CTE although CTEs as so elegant. Let me know what you think. Thanks!

-- @count is passed in as an out param to the stored procedure

CREATE TABLE dbo.#everything (id int, a int, b int, c int);

INSERT INTO #everything 
SELECT id, a, b, c FROM table WHERE a < 2;      

SET @count = @@rowcount;
SELECT TOP (5) id FROM #everything;
DROP TABLE #everything;

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Here's a relatively efficient way to get 5 random rows and include the total count. The random element will introduce a full sort no matter where you put it.

SELECT TOP (5) id,a,b,c,total = COUNT(*) OVER()
  FROM dbo.mytable
  ORDER BY NEWID();

Upvotes: 4

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Assuming you want the top 5 ordering by id ascending, this will do it with a single pass through your table.

; WITH Everything AS 
(
    SELECT id
        , a
        , b
        , c
        , ROW_NUMBER() OVER (ORDER BY id ASC) AS rn_asc
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS rn_desc
    FROM <table>
)
SELECT id
    , a
    , b
    , c
    , rn_asc + rn_desc - 1 AS total_rows
FROM Everything
WHERE rn_asc <= 5

Upvotes: 2

Related Questions