How to retrieve the total row count of a query with TOP

aggregate, count, pagination, sql-server

Solution

Do you want a second query?

SELECT TOP 10
    *, foo.bar
FROM
    T
    CROSS JOIN
    (SELECT COUNT(*) AS bar FROM T WHERE ...) foo
WHERE
    ...
ORDER BY
    ...

OR

DECLARE @bar int
SELECT @bar = COUNT(*) AS bar FROM T WHERE ...
SELECT TOP 10
    *, @bar
FROM
    T
    CROSS JOIN
    (SELECT COUNT(*) AS bar FROM T WHERE ...) foo
WHERE
    ...
ORDER BY
    ...

Or (Edit: using WITH)

WITH cTotal AS
(
    SELECT COUNT(*) AS bar FROM T WHERE ...)
)
SELECT TOP 10
    *, cTotal .bar
FROM
    T
WHERE
    ...
ORDER BY
    ...

Problem

I have a SQL Server 2008 query ``` SELECT TOP 10 * FROM T WHERE ... ORDER BY ... ``` I'd like to get also the total number of the rows. The obious way is to make a second query ``` SELECT COUNT(*) FROM T WHERE ... ORDER BY ... ``` Is there an efficient method? Thanks

Original source

Related problems